If you’ve read my blog for a while you may have seen the following posts:
Well the time has come for me to put together a compilation of ten useful DAX calculations for your Tabular or Power Pivot model (in no particular order so don’t infer any level of ranking or importance from the order they’re posted).
If you’re interested in playing along with me, just download the Contoso Access database which can be found at CodePlex, as this is what I’ll be using for these examples.
And if you are playing along, the calculations from this series will be based on these three calculations unless otherwise stated:
Total Cost:=SUM([TotalCost]) Total Sales Amount:=SUM([SalesAmount]) Total Quantity:=SUM([SalesQuantity])
Mark the Date Table
Since some of these calculations will use advanced DAX time functions, the first thing we need to do is mark the date table. Thankfully, this is very easy. Just select the table in your model that is the date table and click the Mark as Date Table button in the Design ribbon. Then specify the column in your date table that is both a unique identifier for the table and is a date data type. Then click OK. And you’re all set!
10. DAX YTD, QTD and MTD calculations
I decided to lump these calculation types all together since they’re all very similar. This function requires the date table to be marked and for us to use the DATESYTD function.
YTD Total Cost:=CALCULATE([Total Cost],DATESYTD('Date'[Datekey]))
And here are the results so you can see the calculation in action.
QTD Total Cost:=CALCULATE([Total Cost],DATESQTD('Date'[Datekey])) MTD Total Cost:=CALCULATE([Total Cost],DATESMTD('Date'[Datekey]))
9. DAX Previous YTD, Previous Year QTD and Previous Year MTD calculation
Another common calculation I see implemented in BISM solutions is the Previous YTD calculation. It’s very similar to the previous calculation we looked at except now we need to calculate the year to date value for the same day in the previous year. They key to making this calculation work is the SAMEPERIODLASTYEAR function. This function allows us to shift the date one year backward.
Here is the calculation to calculate the Previous YTD value for the Total Cost measure. Just keep in mind that this calculation uses the calculation we previously built.
Previous YTD Total Cost:=CALCULATE([YTD Total Cost],SAMEPERIODLASTYEAR('Date'[Datekey]))
And here are the results. We can see that we are now able to calculate the Previous YTD Total Cost quite easily.
If we want to see the QTD value in the previous year or the MTD value in the previous year, just reference the appropriate measure as seen below.
Previous Year QTD Total Cost:=CALCULATE([QTD Total Cost],SAMEPERIODLASTYEAR('Date'[Datekey])) Previous Year MTD Total Cost:=CALCULATE([MTD Total Cost],SAMEPERIODLASTYEAR('Date'[Datekey]))
8. DAX Previous QTD and Previous MTD calculation
Calculating the previous quarter to date or the previous month to date value for a measure will be a little different since we can’t use the SAMEPERIODLASTYEAR function. For these calculations, we’ll need to use a few different functions to make this work including the DATESBETWEEN, PREVIOUSMONTH (or PREVIOUSQUARTER for the Previous QTD calculation), FIRSTDATE and LASTDATE.
First, lets look at the calculation for calculating the Previous MTD Total Cost. I hope the comments will be helpful in giving you a better understanding of how the calculation is working.
Previous MTD Total Cost:= CALCULATE([Total Cost], DATESBETWEEN('Date'[Datekey], /*DATESBETWEEN function returns a table of days based on begin & end dates.*/ FIRSTDATE(PREVIOUSMONTH('Date'[Datekey])), /*PREVIOUSMONTH gets all the days from the previous month. FIRSTDATE returns the first day of that month.*/ LASTDATE(DATEADD('Date'[Datekey],-1,MONTH)) /*DATEADD allows us to navigate a number of periods back in time. LASTDATE gets the last date.*/ ) )
The Previous QTD calculation looks very similar except I’ll use the PREVIOUSQUARTER function instead of PREVIOUSMONTH and I’ll adjust the DATEADD function expression as seen below.
Previous QTD Total Cost:= CALCULATE([Total Cost], DATESBETWEEN('Date'[Datekey], FIRSTDATE(PREVIOUSQUARTER('Date'[Datekey])), LASTDATE(DATEADD('Date'[Datekey],-1,QUARTER)) ) )
7. DAX Year Over Year Growth calculation
To calculate the year over year growth we’ll need to create two calculations to make this work. The first calculation needs to determine the previous year value of our measure. In this case, I want to calculate the Previous Year Total Sales Amount.
Previous Year Total Sales Amount:=CALCULATE([Total Sales Amount], SAMEPERIODLASTYEAR('Date'[Datekey]) )
And the results:
Now to calculate the growth between the previous year and the current year, we just need to create a calculation to find the difference:
6. DAX Year Over Year Percent Growth calculation
But what if you want to take it a step further and calculate the percentage of growth? Let’s just simply divide the YOY Growth Total Sales Amount measure by the Previous Year Total Sales Amount measure to calculate the percent growth with the following calculation:
YOY Percent Growth Total Sales Amount:=[YOY Growth Total Sales Amount]/[Previous Year Total Sales Amount]
Uh oh. Take a look at the results. It seems we have a problem. This error is caused by dividing by zero since prior to the year 2007 we have no data.
We must rewrite the the calculation to account for this problem. I’m going to use the IFERROR function as seen below.
YOY Percent Growth Total Sales Amount:= IFERROR( [YOY Growth Total Sales Amount]/[Previous Year Total Sales Amount], BLANK() )
Now everything looks great!
Also, instead of using ISERROR to check for division by zero, you could use the DIVIDE function, which will automatically return a blank in the case of division by zero.
YOY Percent Growth Total Sales Amount:=DIVIDE([YOY Growth Total Sales Amount],[Previous Year Total Sales Amount])
This is just part one and the first five calculations. Stay tuned for the next five calculations! In the meantime, leave a comment and let me know if you found this useful! Thank you for reading!