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.

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

  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.

  9. I’m new to Power Query, but I have really enjoyed what I read it can do to eliminate many of the manual steps I’m currently doing within Excel. For instance, I have an Excel spreadsheet that contains data from our phone system. One of the columns is the incoming callers phone number. Typically, this column contains the 10-digit phone number. However, there are times when this column contains a 11-digit phone number. Our SQL data has all our phone numbers stored as a 10-digit number.

    I can manually adjust for this within Excel with a simple formula. If the phone number is in column B2, the formula I use is:

    =(IF(LEN(B2)>10,RIGHT(B2,10),B2)+0)

    Basically, I check the length of the phone number. If it is greater than 10, I take the RIGHT 10-digits, otherwise I take the value in B2. Since the RIGHT function returns a TEXT value, I add a 0 to the returned value so I have a numeric value.

    My question for the group is, how can I replicate this using Power Query, so I never need to do this again?

    • In Power Query:
      1) Select your column
      2) Go to the Transform tab
      3) Click Extract/Last Characters
      4) Enter the value 10 and click OK

      This keeps just the rightmost 10 characters in a text column. If there are 10 characters then nothing changes; if there are more than 10 you just get the last 10. Is that what you want?

  10. That was a very simple way to extract the last 10 characters. However, the value returned is text. Is there a way to have the value numeric? Actually, what would be really nice is to have the other non-numeric values converted to nulls since a few of the values are “restricted” or “unavailable”.

    In the end, I need the 10-digit phone number that is numeric so I can connect it to our SQL data.

    • The easiest way to do this is to set the data type of the column to be a whole number (by clicking on the appropriate icon in the top left-hand corner of the column), then replacing any resulting error values with null

      • If you right-click on the column there’s a Replace Errors menu item. Just enter null into the dialog and the errors will be replaced with null values, not the text “null”

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 )

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: