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:
[sourcecode language='text' padlinenumbers='true'] DayDurationColumn = DATEDIFF(MyTable[Start Date], MyTable[End Date], DAY) YearDurationColumn = DATEDIFF(MyTable[Start Date], MyTable[End Date], YEAR) [/sourcecode]
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:
[sourcecode language='text' ] YearDurationMeasure = DATEDIFF( FIRSTDATE(MyTable[Start Date]), LASTDATE(MyTable[End Date]), YEAR) [/sourcecode]
All very straightforward, then, and much easier than having to calculate these values yourself.