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

0 votes

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?

Oct 8 in Power BI by anonymous
• 4,740 points
32 views

1 answer to this question.

0 votes

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]
                          )
            )
answered Oct 8 by Andi Mohor

Related Questions In Power BI

0 votes
0 answers

How to break datetime field to date field in power bi?

How to break DateTime field to date ...READ MORE

Oct 17, 2019 in Power BI by ch
• 3,450 points
568 views
0 votes
1 answer

How to convert date format in PowerBI?

Change the column data type in excel ...READ MORE

answered Oct 28 in Power BI by anonymous
• 50,100 points
28 views
0 votes
1 answer

How to add a One drive Excel as a Data set in Power BI?

If the one Drive is from your ...READ MORE

answered Oct 29 in Power BI by anonymous
• 50,100 points
42 views
0 votes
1 answer

To expand all lists in a row of lists at the same time without recurring values

Can't promise simple, but I have something ...READ MORE

answered Feb 19, 2019 in Power BI by Upasana
• 8,620 points
2,000 views
0 votes
1 answer

How to get month name from month number in Power BI?

You can use: MonthName = FORMAT(DATE(1, [Num], 1), ...READ MORE

answered Sep 24 in Power BI by Alisha
39 views
0 votes
1 answer
0 votes
1 answer

How to ignore a slicer for one measure, but apply it on another?

In order to ignore Slicer you need ...READ MORE

answered Oct 1 in Power BI by Gitika
• 50,100 points
233 views
0 votes
1 answer
0 votes
1 answer

How do I get my DAX measure to calculate grouped values?

Try this: Total Usage:= SUMX( VALUES(MyTable[SensorID]), [Usage]) An alternative ...READ MORE

answered Oct 8 in Power BI by Gitika
• 50,100 points
32 views