If you’ve read my blog for a while you may have seen the following posts:

Ten MDX Calculations For Your Cube (part 1)
Ten MDX Calculations For Your Cube (part 2)

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 new to Power Pivot or SSAS Tabular, I suggest you start here.

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])

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!

Let’s begin.

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.

If you want to create a QTD or MTD calculation, just use the DATESQTD or DATESMTD function instead of DATESYTD:

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]))

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]))

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)) 
    )
)

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])
        )

Now to calculate the growth between the previous year and the current year, we just need to create a calculation to find the difference:

YOY Growth Total Sales Amount:=[Total Sales Amount]-[Previous Year Total Sales Amount]

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]

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])

Resources

If you’d like to learn more about Power Pivot, sign up for this free training webinar next week September 10th at 12 PM CDT

For great information on DAX calculations and other patterns, definitely visit DAXPatterns.com.

For the full DAX reference guide, check out this link.

Feedback?

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!