Using DateDiff() To Calculate Time Intervals In DAX

One of the most useful new additions to DAX in Excel 2016 and the Power BI Designer is the DateDiff() function. It does exactly what you would expect: calculate the amount of time in between two dates, and express that value as either seconds, minutes, hours, days, weeks, months, quarters or years.

Here’s a very simple table of dates:

image

With this table loaded into the Power BI Designer, you can add new calculated columns to the table by clicking the New Column button on the ribbon. Here are two calculated column definitions that give the number of days and the number of years between the Start Date and the End Date on each row:

DayDurationColumn = 
DATEDIFF(MyTable[Start Date], MyTable[End Date], DAY)

YearDurationColumn = 
DATEDIFF(MyTable[Start Date], MyTable[End Date], YEAR) 

The output is pretty much what you’d expect:

image

It is of course also possible to create measures that use the DateDiff() function to, for example:

YearDurationMeasure = 
DATEDIFF(
FIRSTDATE(MyTable[Start Date]), 
LASTDATE(MyTable[End Date]), 
YEAR)

image

All very straightforward, then, and much easier than having to calculate these values yourself.

11 thoughts on “Using DateDiff() To Calculate Time Intervals In DAX

  1. How would you calculate age when you want to know the age at that point in time? For example let’s assume my birthdate is Dec 1, 1974 and today is July 14, 2015. Using DateDiff and returning the year will calculate my age as 41 when in fact I’m considered 40 until Dec 1. Using DateDif in Excel 2013 produces 40. TSQL will calculate 41. I’d be fine if all MS apps were consistent.

      • In situations like this the best way of solving the problem is to do as much as possible in the model, and as little as possible in DAX. My advice would be to add a column to your Date table containing the number of working hours for each day, and then it would be simple to do your calculation by creating a Sum measure on that column.

      • Thanks for the quick reply Chris. I need to calculate help desk ticket duration and staff is being evaluated, so it’s important to take into account the ticket start time and end times as well. I’m going to try to get a variant of the formula below from powerpivotpro.com to work … thanks, Mark

        Bob Phillips says:
        April 30, 2014 at 8:23 am
        That didn’t quite come out correctly
        =(
        CALCULATE(
        SUM(Dates[IsWorkday]),
        DATESBETWEEN(
        Dates[Date],
        TwoDates[Column1],
        TwoDates[Column2]
        )
        )
        )
        – ( WEEKDAY( TwoDates[Column1], 2) 5, 0, MOD( TwoDates[Column2], 1 ) – “08:00:00″*1)

      • Hello, Chris!
        I am faced up with the same problem as Matt described.
        For example, if my birthdate is Sep 16, 1993 an now is June 6, 2017, DateDiff(09/16/1993, 06/06/2017, YEARS) returns 24. But as I know, I am 23 now (until Sep 16, 2017).
        So, how do you think what is the best way to calculate the age correctly?

    • I forgot to reply back to this a long time ago. I think I solved this issue with the following calculation:
      Employee Age = INT(DATEDIFF(Data[Birth Date], TODAY(), DAY)/365)

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s