Sum for most recent date available

[mwmchugh215]

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:

Prior Date Sales =

CALCULATE(SUM(RecordDate[Sales], RecordDate[RankNo] = RecordDate[RankNo]-1)

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

[Anonymous]

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

[mwmchugh215]

Hi though this solution did not work for me I was able to resolve on my own.

[Anonymous]

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

[smpa01]

@mwmchugh215  you can do this

Prior Sales = CALCULATE([Total Sales], OFFSET(-1,ALL(data[Record Date]),ORDERBY(data[Record Date])))

[mwmchugh215]

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.

[smpa01]

@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.