DAX – Time Comparison

How to write a DAX formula, when you want to compare the Sales to a previous time period. For instance, what would be the last year’s sales during the same period or maybe last month or last quarter sales?

Let us see how we can compute that using simple DAX.

Calculating sales for the previous year, previous quarter or previous month

Firstly, Add a new measure and calculate Total Sales

Next, using Total Sales Measure we can calculate sales for last year, last quarter, or last month using DateAdd function

Add new measure and write the below DAX formula

Sales LY = CALCULATE([Total Sales], DATEADD(Dates[Date],-1,YEAR))

We are using Calculate, as it changes the current context.

We have already calculated Total Sales, hence we can just use Total Sales measure as an expression instead of rewriting the whole formulae and is easy to understand.
Using the same DAX formula, we can calculate Sales for the last quarter or last month
Instead of Year, we will be using Quarter
Sales LQ = CALCULATE([Total Sales], DATEADD(Dates[Date],-1,QUARTER))

Similarly, we can now write the DAX for Sales in last month

Sales LM = CALCULATE([Total Sales], DATEADD(Dates[Date],-1,MONTH))