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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s