topic Sum for most recent date available in DAX Commands and Tips

Sum for most recent date available

Anonymous — Mon, 13 May 2024 17:27:26 GMT

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

Re: Sum for most recent date available

smpa01 — Mon, 13 May 2024 19:30:05 GMT

Anonymous you can do this

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

Re: Sum for most recent date available

Anonymous — Mon, 13 May 2024 19:48:06 GMT

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.

Re: Sum for most recent date available

smpa01 — Mon, 13 May 2024 19:51:39 GMT

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

Re: Sum for most recent date available

Anonymous — Wed, 15 May 2024 06:40:24 GMT

Hi, Anonymous

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

Re: Sum for most recent date available

Anonymous — Thu, 16 May 2024 16:37:49 GMT

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

Re: Sum for most recent date available

Anonymous — Mon, 27 May 2024 02:37:32 GMT

Hi, Anonymous

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