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, 2020 in Power BI by anonymous
• 8,910 points
202 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, 2020 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 18, 2019 in Power BI by ch
• 3,450 points
752 views
0 votes
1 answer

How to convert date format in PowerBI?

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

answered Oct 29, 2020 in Power BI by anonymous
• 65,970 points
407 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, 2020 in Power BI by anonymous
• 65,970 points
140 views
0 votes
1 answer

How to convert eight digit yyyymmdd to date using DAX in Power BI ?

You can go through this: column = IFERROR( ...READ MORE

answered Dec 8, 2020 in Power BI by Gitika
• 65,970 points
1,081 views
0 votes
1 answer
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, 2020 in Power BI by Alisha
1,002 views
0 votes
1 answer

How do I count rows in one table based on values in another table using DAX?

If the tables are related, this is ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,970 points
9,439 views
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, 2020 in Power BI by Gitika
• 65,970 points
2,754 views
0 votes
1 answer

In DAX, how do I RETURN the sum of a calculated column from a DAX Table Variable (created via ADDCOLUMN)?

You can access column variables of previously ...READ MORE

answered Oct 8, 2020 in Power BI by Gitika
• 65,970 points
1,937 views
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, 2020 in Power BI by Gitika
• 65,970 points
148 views