Search code examples
powerpivotdax

DAX Query on (Day Over Day)

I have the Sales table and a Date table

enter image description here

I wrote the below query to calculate the DOD Sales

Sales Volume := SUM([Sales])

Sales Volume (Prev) := CALCULATE([Sales Volume], PREVIOUSDAY('Date'[Date])

Sales Volume (DOD) = DIVIDE([Sales Volume]-[Sales Volume (Prev)],[Sales Volume (Prev)])

However, these query above will calculate DOD based on continuous day of a month. My concern is I would like to calculate only those Order Date. For example, I would like to compare Sales on 4/12/2016 and 1/12/2016. ((50-20)/20). How should i amend the query to achieve that?

Solution

  • You have to create a calculated column to get the previous date:

    Previous Date =
CALCULATE (
    MAX ( [Order Date] ),
    FILTER ( ALL ( 'Table' ), [Order Date] < EARLIER ( 'Table'[Order Date] ) )
)

    Then just create the measures with the following expressions:

    Sales Volume := SUM('Table'[Sales])


    Sales Volume Prev :=
CALCULATE (
    SUM ( [Sales] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Order Date] = MAX ( [Previous Date] ) )
)


    Sales Volume (DOD) :=
DIVIDE ( [Sales Volume] - [Sales Volume Prev], [Sales Volume Prev] )

    Let me know if this helps.