DAX Query on (Day Over Day)

Question

I have the Sales table and a Date table

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?

alejandro zuleta alejandro zuleta · Accepted Answer · 2016-12-01T13:32:33

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.

DAX Query on (Day Over Day)

1 Answers