DAX: How to calculate the difference between two dates using DATEDIFF

by

In this article, we will show you how can calculate the difference between two dates in Power BI using DAX.

The DAX function we use to achieve this is DATEDIFF.

This returns the difference between two date fields and can be expressed in days, weeks, months, seconds, and more.

We go over different common questions with DATEDIFF, such as using it with a filter or for dates in the same column.

If you want to calculate the difference between two dates in Power Query, rather than Power BI, we have a separate guide for this, as well as how to calculate the number of working days between two dates in Power BI.

How to use DATEDIFF to calculate the difference between two dates in Power BI

The DATEDIFF DAX function can be used in Power BI to calculate the difference between two dates, as either a custom column or measure.

The syntax for DATEDIFF DAX function is:

= DATEDIFF(Start Date, End Date, Interval)
  • Start Date: The date you want to count the difference from
  • End Date: The date you want to count the difference to
  • Interval: How you want the difference to be expressed. This can be in either days, hours, minutes, months, quarters, seconds, weeks or years

Here is an example table in Power BI that we will use to demonstrate DATEDIFF.

It has staff members of a company, when they were hired and when they left:

If we want to calculate the number of days between each staff members date started and date left, we would use the following formula:

DateDiff = DATEDIFF(Staff[Date Started],Staff[Date Left],DAY)

In this case, we have used the following parts of the formula:

  • Start Date: Date Started column from our table
  • End Date: Date Left column from our table
  • Interval: DAY, since we want the number of days between the two dates.

Here is the result:

If we want to know the number of months each employee was at the company, we would simply swap ‘DAY’ for ‘MONTH’ in the interval section, as below:

DateDiff = DATEDIFF(Staff[Date Started],Staff[Date Left],MONTH)

This will then return the following result:

How to use DATEDIFF to calculate the difference between a date and today

If you want to calculate the difference between a date and today in Power BI, you need to use the TODAY() DAX function inside your DATEDIFF formula, as either the start date or end date.

Here is an example of a custom column to calculate the difference between the date each staff member left and today:

DateDiff Today = DATEDIFF(Staff[Date Left],TODAY(),DAY)

Here is the result:

Of course you can change the interval to your desired time frame, but in this example we have calculated the difference in days.

How to use DATEDIFF with a filter

If you want to calculate the difference between two dates in Power BI based on certain criteria, you can use the DATEDIFF DAX function along with a filter.

In this example, we have used an IF statement to only return the DATEDIFF answer based on the country:

DateDiff Filter = IF(Staff[Country] = "USA",DATEDIFF(Staff[Date Started],Staff[Date Left],DAY))

Here is the result:

Using the example below, if we want to find out how long it has been since Staff ID 1 left the company and then rejoined, we can use DATEDIFF and FILTER.

We need to calculate the difference between the last date left and the last date started per staff ID.

Firstly, we will create two parameters to use as the Start Date and End Date.

We will use MAXX and MINX along with FILTER and EARLIER to return the last date left and started per staff ID.

These will then be used in our DATEDIFF formula. We have also wrapped this in an IF statement to only return the answer when needed.

Here is the custom column:

Days Since Left and Rehired = 

VAR MaxEndDate =
    MAXX(
        FILTER('Staff (2)', 'Staff (2)'[Staff ID] = EARLIER('Staff (2)'[Staff ID])),
        'Staff (2)'[Date Left]
    )

VAR MinStartDate =
    MINX(
        FILTER('Staff (2)', 'Staff (2)'[Staff ID] = EARLIER('Staff (2)'[Staff ID]) && 'Staff (2)'[Date started] > MaxEndDate),
        'Staff (2)'[Date started]
    )

RETURN IF(ISBLANK('Staff (2)'[Date Left]), DATEDIFF(MaxEndDate, MinStartDate, DAY),BLANK())

Here is the result:

How to use DATEDIFF for dates in the same column

If you want to calculate the difference between two dates that are in the same column in Power BI, you can use DATEDIFF along with a custom parameter.

Using the example above, we want to calculate the difference between each Date Started for each Staff ID.

We need to create a parameter and use the TOPN function along with FILTER and EARLIER to return the latest Date Started per Staff ID. This parameter is then used with MINX to provide the Start Date for our DATEDIFF formula.

Here is the custom column: 

DateDiff Same Column = 

VAR DaysSinceStarted = 
TOPN(
    1,
    FILTER('Staff (2)','Staff (2)'[Staff ID] = EARLIER('Staff (2)'[Staff ID]) && 'Staff (2)'[Date Started] < EARLIER('Staff (2)'[Date Started])),
    'Staff (2)'[Date Started],DESC)
RETURN
DATEDIFF(MINX(DaysSinceStarted,'Staff (2)'[Date Started]),'Staff (2)'[Date Started],DAY)

Here is the result:

How to use DATEDIFF with two tables

If you want to calculate the difference between two dates in different tables in Power BI, you can use the DATEDIFF and RELATED DAX functions to do this.

We will use the same staff table as an example, but we also have another table with the dates that staff members have been promoted.

Here is the example table:

We need to ensure that there is a relationship between our original staff table and our new promotions table, as below:

These tables are joined by the Staff ID.

We want to find out how long it took for staff members to be promoted since they were hired.

To do this we need to calculate the number of days between the Date Started from the Staff table, and the Date Promoted from the Promotions table.

We create a custom column in Power BI and use the DATEDIFF DAX function. For the End Date, we use RELATED to bring through the Date Promoted.

Here is the custom column: 

Days since Promoted = DATEDIFF(Staff[Date Started],RELATED(Promotions[Date Promoted]),DAY)

Here is the result:

Leave a Comment