How to measure as date variable when calculating time periods in Powerpivot

Question

What I would like to is to present a number of different calculations depending on the time frame.

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

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

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?

Answer 1

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

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]
                          )
            )