DAX DateDiff Function

The DATEDIFF function in DAX – unlike its equivilent in SQL returns an error instead of a negative value if the start date is greater than the end date. Thanks for keeping your data languages consistant Microsoft!!

I came up with a work-around using the IFERROR() function and reversing the terms.

ColumnName = IFERROR(DATEDIFF(start_date,end_date,DAY),
(DATEDIFF(end_date,start_date,DAY))*-1)

Using this syntax will result in the same outcome as using the SQL DATEDIFF() function. Its quite simple – if the first DATEDIFF() results in an error then flip the values in the DATEDIFF() then multiple by -1 to give your amount of days as a negative number.

Advertisements