Forum Discussion

SC30GSWNBA's avatar
SC30GSWNBA
Frequent Visitor
2 years ago

DAX Function to create sales difference between two consecutive dates

Hello Everyone,   I am stuck in creating DAX function to create sales difference between two consecutive dates. I have a data in the following table shown below -  As_of_Date Sales 07-05-20...
Need Help
Show and Tell
Tips and Tricks
Ritaf1983's avatar
Ritaf1983
Super User
2 years ago

Hi SC30GSWNBA  you can create 2 measures :

PreviousDateSales =
VAR CurrentDate = MAX('Sales table'[As_of_Date])
VAR PreviousDate = CALCULATE(MAX('Sales table'[As_of_Date]), 'Sales table'[As_of_Date] < CurrentDate)
RETURN
CALCULATE(SUM('Sales table'[Sales]), 'Sales table'[As_of_Date] = PreviousDate)
 
For Delta :
Delta = [sales_]-[PreviousDateSales]
Result :

PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

SC30GSWNBA's avatar
SC30GSWNBA
Frequent Visitor
2 years ago

The PreviousDate VAR is returning me 31st Dec 2024. May be that's the reason why it is not working.

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi SC30GSWNBA  i see that all results are correct....

    Did you download the pbix to follow thr steps?

    If yes please show what is wrong

    • SC30GSWNBA's avatar
      SC30GSWNBA
      Frequent Visitor
      2 years ago

      Yes, I downloaded your file. I am connected to a database and when I apply MAX(Sales Data[Date]), it gives me 31st Dec 2024 as the output rather than the latest date. Not sure why that is happening.

      • Ritaf1983's avatar
        Ritaf1983
        Super User
        2 years ago

        Unfortunately, I can't determine the exact problem without access to your file.

        There's an alternative method to access the value in the row above within a visualization context using the offset function.

        Regrettably, I'm currently not at my computer and cannot provide an example. However, you can refer to the guide linked below:

        https://youtu.be/C3QY0DWts8w?si=b5JhPQwq51lLw1tO