Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table that records sales by date. I need to be able to display the total sales for each date and also display the prior date's total. What is a measure in dax that I can use to sum based on whatever the next most recent date is?
I tried doing a RANKX column on the record dates, which works correctly in providing a numbered rankings for date, then used the below:
However when I use this measure in a table with the date and total sales, the Prior Date Sales is blank.
Is there anything I'm missing with this measure?
Sample of data and the result I'm trying to accomplish:
Table:
Record Date | Sales |
5/13/2024 | 100 |
5/9/2024 | 200 |
5/9/2024 | 200 |
5/2/2024 | 300 |
5/2/2024 | 300 |
4/28/2024 | 400 |
4/28/2024 | 100 |
Totals:
Record Date | Total Sales | Prior Date Sales |
5/13/2024 | 100 | 400 |
5/9/2024 | 400 | 600 |
5/2/2024 | 600 | 500 |
4/28/2024 | 500 |
Hi, @mwmchugh215
Thanks for @smpa01 reply. Have you resolved your issue now, happy to help, have you checked that your data types are as expected, or can you share the pbix file with no sensitive data so it's quicker to determine why your data is not showing up.
Best Regards,
Yang
Community Support Team
Hi though this solution did not work for me I was able to resolve on my own.
Hi, @mwmchugh215
Glad you solved the problem, if you don't mind could you attach your solution so that someone with a similar problem can find the solution, thanks in advance!
Best Regards,
Yang
Community Support Team
@mwmchugh215 you can do this
Prior Sales = CALCULATE([Total Sales], OFFSET(-1,ALL(data[Record Date]),ORDERBY(data[Record Date])))
I was able to create the measure with no errors however still getting blank values in the table. I'll receive a total value for prior date when I add in total sales for that date as a column value. But the prior date values for each is still blank.
@mwmchugh215 I tried to answer as per your sample data and desired output provided previously as you can see in the screenshot. I am not sure what you are referring to. please provide sample data and exact desired output.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
72 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |