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.