Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Sum for most recent date available

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 DateSales
5/13/2024100
5/9/2024200
5/9/2024200
5/2/2024300
5/2/2024300
4/28/2024400
4/28/2024100

 

 Totals: 

Record DateTotal SalesPrior Date Sales
5/13/2024100400
5/9/2024400600
5/2/2024600500
4/28/2024500 
"Dates"
"DAX help"

6 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    2 years ago

    Anonymous  you can do this

     

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        smpa01
        Community Champion
        2 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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



    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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