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_DateSales
07-05-202534
07-05-202534
07-05-202534
08-05-202554
09-05-202523
09-05-202523
10-05-202556
10-05-202556
10-05-202556
10-05-202556
10-05-202556
13-05-202512
13-05-202513
13-05-202514
14-05-202523
14-05-202523
15-05-202545
16-05-202567
17-05-202589
17-05-202589
17-05-202589
17-05-202589
17-05-202589
17-05-202589
20-05-202512
20-05-202512
20-05-202512
21-05-202534
21-05-202534
22-05-202556
22-05-202556
22-05-202556
23-05-202578
24-05-202556
27-05-202598
27-05-202598
27-05-202598

 

I need to create a DAX measure to calculate daily total sales and a T-1 sales column to create sales difference between two consecutive days. 

Also there is no sales on weekends, so we need to take care of no sales on weekends while calculating the measure - Previous Day Sales

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • 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