Power Pivot Principles: The A to Z of DAX Functions – DATEDIFF

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DATEDIFF.

The DATEDIFF function

The DATEDIFF function is one of the Date and Time functions.  It returns the number of interval boundaries between two dates.  It operates using the following syntax:

There are three [3] arguments in this syntax:

• date1: this is required, and must be a scalar datetime value
• date2: this is also required, and also must be a scalar datetime value
• interval: this argument is required, and it is the interval to use when comparing dates.  The value can be one of the following: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

There are a few key notes about this function:

• The result will be positive if date2 is larger than date1
• In previous versions of the DAX engine (earlier than 2018), this could provide an error if date1 is larger than date2.

We can use DATEDIFF to calculate the difference between 1 January 2020 and 15 December 2022 in years, quarters, months, and days.  First, we write the following DAX query:

This will yield the following result:

Alternatively, you can consider this example in Power Pivot where we have a table named Date_Table:

Then we write the following DAX expression to calculate the difference in days, months and years between Start Date and End Date:

The final result will be as follows:

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.