Calculate the difference between two dates in DAX
Another quick Data Analysis eXpressions (DAX) update, given that I’ve been playing about with PowerPivot again this week. Following on from my exertions trying to work out how to return a month name in DAX, I found myself looking for a DAX equivalent of SQL Server’s DATEDIFF function. Fortunately, this is reasonably straightforward. All we need to do is perform a regular subtraction on two dates and multiply the result by 1.0, which will return the number in serial date-time (the number of days since “1900-Jan-0”, which is how Excel stores dates).
The expression below will take two date values (in my example the date now – excluding the time portion, hence my using the DATE() function rather than just taking the result of the NOW() function as-is). The other date is constructed from a string or integer [DateKey] field, which I loaded from a column in my PowerPivot model.
=1. * (DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())) - DATE(LEFT([DateKey],4),LEFT(RIGHT([DateKey],4),2),RIGHT([DateKey],2)))
Extremely simple stuff, but also very useful. If anyone knows any other handy DAX shortcuts, let me know in the comments below. As I play about with more DAX, I may actually compile a cheat sheet here for quick reference.