Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm exploring Power BI, and I seem to be stuck on a simple thing I can't figure out.
I built this simple table visual:
For some reason, the total is displayed for DATESINBETWEEN column, but not for DATESINPERIOD. They are formatted the same way (decimal number, $ currency). Here's the formulas for both of them:
DATESBETWEEN = CALCULATE([Sum_Sales],DATESBETWEEN(Dates[Date],STARTOFMONTH(Dates[Date]),ENDOFMONTH(Dates[Date])))
DATESINPERIOD = CALCULATE([Sum_Sales],DATESINPERIOD(Dates[Date],STARTOFMONTH(Dates[Date]),1,MONTH))
What I'm trying to do is to display the total for DATESINPERIOD. Could anybody please tell me what I'm doing wrong?
Thank you for your help!
Solved! Go to Solution.
I think I see what you are saying now. First things first, your dates in period formula should be a -1 (maybe 1 with start of month could work, but never seen it that way so not sure)
Dates In Period 1 Month = CALCULATE( [Total Sales] , DATESINPERIOD( 'Dimension Date'[Date], LASTDATE('Dimension Date'[Date]), -1, MONTH ) ) )
Second issue is to be sure that your Calendar table is set as a date table to utilize the time-intelligence functions correctly.
In the Data view --> Modeling--> Calendars--> Mark as Data table:
Third thing to remember is that these time intelligence functions return a list of dates as a filter.
Then the issue with the totals is all about context and more specifically filter context in this situation. The only filter we have here are the Dates coming from your table. And why you only sometimes get a total DATESINPERIOD is because of that. So what is the DatesInPeriod formula above really saying?
Hopefully that makes sense so far. Now the next natural question is why does DATESBETWEEN always return a total?
Refreshed on what our formula is for that:
Dates Between 1 Month = CALCULATE ( [Total Sales], DATESBETWEEN ( 'Dimension Date'[Date], STARTOFMONTH ( 'Dimension Date'[Date] ), ENDOFMONTH ( 'Dimension Date'[Date] ) ) )
So remember in Totals we are dealing with all the dates available in the current filter context. So, as your table is set up now, the Total has the entire list of dates available. So the function picks up the Start and the End of that list. In the example above that list ended at 12/31/16 and began 1/1/13. That's the list of dates that filters from the Calendar Table to the sales table, and that is what you see. So while the end date (12/31/16) has no sales all all the other dates did, so you get a total. That is also why you will see repeat values at the day level ( because as it is set up now you are telling the function to look at the current date of the fitler context and give the start and end of that date:
Just something to be aware of.
I hope that helps clear it up (and not make it worse. DAX is basically all about context. Simple concept for sure, but there's nothing easy about it, but that's what it makes it fun!
-Nick
Also totals wont be dsiplayed is there are null values in the cell.
You can use IFERROR function to convert them to 0.
DATESINPERIOD = CALCULATE(IFERROR([Sum_Sales].0),DATESINPERIOD(Dates[Date],STARTOFMONTH(Dates[Date]),1,MONTH))
If this was working as you hoped, what would the total be there? Big thing (if not the biggest) is all about context. DATESINPERIOD is going back 1 month from where you are in the current filter context (i.e. the date row in this case). Totals are just the absence of filters. So what date would DAX use as the starting point for DATESINPERIOD?
The total for DATESINPERIOD would be the same as DATESBETWEEN. And my date ranges between July 4th 2015 and May 6th 2017 (first sales date is 07/04/15 and the last sales date is 05/06/17).
Also, if I change my formula to limit my range to July 4th 2015 to July 4th 2017, I get the correct total. So I guess I'm confused why I don't get the same total if I run a slightly different formula. I'm not sure what I'm missing to be honest...
I think I see what you are saying now. First things first, your dates in period formula should be a -1 (maybe 1 with start of month could work, but never seen it that way so not sure)
Dates In Period 1 Month = CALCULATE( [Total Sales] , DATESINPERIOD( 'Dimension Date'[Date], LASTDATE('Dimension Date'[Date]), -1, MONTH ) ) )
Second issue is to be sure that your Calendar table is set as a date table to utilize the time-intelligence functions correctly.
In the Data view --> Modeling--> Calendars--> Mark as Data table:
Third thing to remember is that these time intelligence functions return a list of dates as a filter.
Then the issue with the totals is all about context and more specifically filter context in this situation. The only filter we have here are the Dates coming from your table. And why you only sometimes get a total DATESINPERIOD is because of that. So what is the DatesInPeriod formula above really saying?
Hopefully that makes sense so far. Now the next natural question is why does DATESBETWEEN always return a total?
Refreshed on what our formula is for that:
Dates Between 1 Month = CALCULATE ( [Total Sales], DATESBETWEEN ( 'Dimension Date'[Date], STARTOFMONTH ( 'Dimension Date'[Date] ), ENDOFMONTH ( 'Dimension Date'[Date] ) ) )
So remember in Totals we are dealing with all the dates available in the current filter context. So, as your table is set up now, the Total has the entire list of dates available. So the function picks up the Start and the End of that list. In the example above that list ended at 12/31/16 and began 1/1/13. That's the list of dates that filters from the Calendar Table to the sales table, and that is what you see. So while the end date (12/31/16) has no sales all all the other dates did, so you get a total. That is also why you will see repeat values at the day level ( because as it is set up now you are telling the function to look at the current date of the fitler context and give the start and end of that date:
Just something to be aware of.
I hope that helps clear it up (and not make it worse. DAX is basically all about context. Simple concept for sure, but there's nothing easy about it, but that's what it makes it fun!
-Nick
Thank you so much for all the details @Anonymous! Your explanation made me realize something about DatesInPeriod function I didn't see before.
My problem was that I expected the same Total result from DatesInPeriod as from DatesBetween, but I didn't realize that DatesBetween is a finite function which gives a sum total, and DatesInPeriod is a continuous function that gives the last value as a total.
I've been following your steps, and when I tried filtering dates, it all made sense.
I greatly appreciate your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |