Report Portal

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:

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.


chris-webb

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/ .


Tags: dax, tabular

 

2007-2015 VidasSoft Systems Inc.