Using DateDiff() To Calculate Time Intervals In DAX

Reposted from Chris Webb's blog with the author's permission.

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 =  DATEDIFF( FIRSTDATE(MyTable[Start Date]),  LASTDATE(MyTable[End Date]),  YEAR) 
 

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

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .