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:


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:


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

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


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

17 responses

  1. Pingback: Dew Drop – June 23, 2015 (#2040) | Morning Dew

  2. 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 to work … thanks, Mark

        Bob Phillips says:
        April 30, 2014 at 8:23 am
        That didn’t quite come out correctly
        – ( 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)

  3. Pingback: Das neue Excel 2016 | Teil 2: Power Pivot | Linearis :: BI für die Fachabteilung

  4. Chris I used your syntax, and it seems to have worked. However, when I try to graph it, I get this error.

    MdxScript(model) (3,55) Calculation error in measure’aztecaREQUEST'[HOURDURATIONMEASURE]: A date column containing duplicate dates was specified in the call to function ‘FIRSTDATE. This is not supported.

  5. how to Calculate the no of days between two dates using POWER PIVOT 2013.
    Activity Sub_Activity Start END
    1 1 10-Oct-2016 15-Oct-2016
    2 1 20-Sep-2016 30-Oct-2016
    3 2 12-Dec 2016 15-Dec-2016
    4 2 10-Dec-2016 13-Dec 2016

    I wanted to make summary as
    Sub_Activity start(min) No_of_days(maximum of end date – minimum of Start date)
    1 10-Oct-2016 20 days (30-oct-2016 – 10-oct-2016)
    2 10-Dec-2106 5 days (15-dec-2016 – 10-dec-2016)

    I am able to do using DAX POWER PIVOT 2016 using DATEDIFF, Please advise how to write using DAX 2013

  6. Hi. Would you happen to know how I could show this data in a table split by Month. So if start date was 01/06/2017, and end date was 05/08/17, a “1 would appear in the table under Jun,Jul,Aug 2017. I can do ,many other things with DAX, but don’t seem to be able to crack this…

  7. Pingback: Fourteenth Blog Birthday « Chris Webb's BI Blog

  8. Hi Chrish,

    I need your help on this below scenarios, We have data in column Date ,A and B.

    I need the output like other column “Result”

    steps :

    A- B = Result

    then take value from Result – next row value of B

    until the result value get zero or -ve.

    once you get zero then need to divide the previous value of result and corresponding vale of B . which i am highlight as red color.

    data will be weekly so this calculation will work if i select any week.

    so ,this should be in measure. not by calculated column.

    Date A B Result
    3/1/2019 1000 100 900
    2/22/2019 2000 500 400
    2/15/2019 15000 200 200
    2/8/2019 3000 100 100
    2/1/2019 4500 100 0
    1/25/2019 2400 1200

    2/22/2019 2000 500 1500
    2/15/2019 15000 200 1300
    2/8/2019 3000 100 1200
    2/1/2019 4500 100 1100
    1/25/2019 2400 1200 -100

    Could you please help on this.

Leave a Reply

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

You are commenting using your 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: