10 DAX Calculations

10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

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

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!

Power Pivot mark as date table

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.

YTD DAX calculation results

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

YTD QTD MTD DAX calculation

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.

Previous YTD DAX calculation

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:

DAX previous year calculation

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]

DAX Year over year growth calculation

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.

image

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!

  image

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!

15 thoughts on “10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)”

  1. Thanks for eventually getting around to part 2. Your #5 “Use an Inactive/Custom Relationship” was of great use to me.

  2. Good Article, have a question: how i can divide a row value by a total of a measure ?

    have this:

    ID Value
    1 10
    2 20
    3 30
    4 40

    and a measure TotalValue = 60 (filtered only for ID=1, 2 And 3)

    and i want this calculated column

    ID Value New Calculated Column
    1 10 10 / 60 = 0.166
    2 20 20 / 60 = 0.333
    3 30 30 / 60 = 0.500
    4 40 40 / 60 = 0.666

  3. thanks for your quickly answer

    but what happend if the measure depends on the user filters? i should use the ALLSELECTED filter?

Leave a Reply