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

Screenshot (204)

Screenshot (207)

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.
Screenshot (205)
Screenshot (206)
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))
Screenshot (211)
Screenshot (208)

 

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

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

Screenshot (212)

Screenshot (210)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s