Measure as date variable when calculating time periods in Powerpivot

My situation is as following:

This is a Powerpivot solution developed in Excel 2013 (32-bit). I got a transaction table containing transactions with an amount, a category and a posting date. What I would like to to is to present a number of different calculations depending on the time frame.

Sum of amount of current day of import (all the transations with the latest posting date available).
Sum of amount Month-to-date (the current month of the latest transaction)
Sum of amount same period last month (Month-to-date minus one month)
Sum of amount last month (the totals for whole last month)

So, idea is to create a "Current day" measure as a stand point for all the other measures.

[Current day] = LASTDATE('TransactionTable'[Posting Date])

Before summarizing things I wanted to create measures that would represent the start and end date for each period (to display in the report and to make easier measures), this is where I run into trouble.

[First day of current month] = STARTOFMONTH([Current Day])

Gives me the error: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

And with this I would like to end up with something like this for current month:

[Sum of amount current month] = CALCULATE(SUM('Transactiontable'[Amount]);DATESBETWEEN('DateTable'[Date]; [First day of current month];[Current day]))

And this for previous month total:

[First day of previous month] = DATEADD([First day of current month];-1;MONTH)
[Last day of previous month] = EOMONTH([Current day];-1)

CALCULATE(SUM('Transactiontable'[Amount]);DATESBETWEEN('DateTable'[Date]; [First day of previous month]; [Last day of previous month]))

It feels like I am not using the measures the "right" way... Basically I want to create dynamic measures that will change the timeframe depending on what the latest posting date is in the transaction table. Is this the way to go at all?

Thanks guys,

Solution

First you need to use ALL()

You were pretty close - the trick to getting [CurrentDate] right is use of the ALL() function. This handy function overrides any filter conditions you have.

So let's look at your [CurrentDate] measure that uses LASTDATE('TransactionTable'[Posting Date]). If your pivot table has months as row labels, this will happen:

enter image description here

The row context alters the output of [CurrentDate]. Not what we want. So you need to override the filter condition, like this.

[CurrentDate] = CALCULATE(LASTDATE(TransactionTable[Posting Date])
                          ,ALL(TransactionTable)
                          )

Then you need FILTER()

Then to sum the amount for [CurrentDay] we do this:

[SumAmountCurrentDay] = CALCULATE([SumAmount]
                       ,FILTER(TransactionTable
                               ,TransactionTable[Posting Date]=[CurrentDay]
                               )
                        )

We need to use FILTER() because it's a more complicated criteria than CALCULATE can handle by default. FILTER() explicitly tells CALCULATE() which table it needs to filter on - although it might be obvious to us, it isn't to PowerPivot.

Here are the rest of the formulas you need, of varying complexity but mostly reusing functions you've listed above, plus ALL() and FILTER().

[FirstDayOfCurrentMonth]

=CALCULATE(STARTOFMONTH(TransactionTable[Posting Date])
           ,ALL(TransactionTable)
           ,FILTER(TransactionTable
                   ,TransactionTable[Posting Date]=[CurrentDay]
                   )
           )

[SumAmountCurrentMonth]

=CALCULATE([SumAmount]
           ,DATESBETWEEN(DateTable[Date]
                         ,[FirstDayOfCurrentMonth]
                         ,[CurrentDay]
                         )
           )

[FirstDayOfPrevMonth]

=CALCULATE(STARTOFMONTH(TransactionTable[Posting Date])
           ,ALL(TransactionTable)
           ,FILTER(TransactionTable
                   ,TransactionTable[Posting Date]=
                         CALCULATE(dateadd(LASTDATE(TransactionTable[Posting Date])
                                           ,-1
                                           ,month
                                           )
                                   ,ALL(TransactionTable)
                                   )
                   )
            )

[LastDayOfPrevMonth]

=CALCULATE(ENDOFMONTH(TransactionTable[Posting Date])
          ,ALL(TransactionTable)
          ,FILTER(TransactionTable
                  ,TransactionTable[Posting Date]=
                        CALCULATE(dateadd(LASTDATE(TransactionTable[Posting Date])
                                          ,-1
                                          ,month
                                          )
                                   ,ALL(TransactionTable)
                                   )
                  )
            )

SumAmountPrevMonth

=CALCULATE([SumAmount]
           ,DATESBETWEEN(DateTable[Date]
                         ,[FirstDayOfPrevMonth]
                         ,[LastDayOfPrevMonth]
                          )
            )