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
=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.

Data Solutions Architect at Indicia. More years than I care to remember wrangling SQL Server and .NET. Data geek, football fan, and Xbox gamer. Check out my other blog Press B To Parent if you're a gamer.

Leave a Reply

1 Comment

  1. Pingback: Power Pivot DAX: Difference between two DATE values | Paras Doshi - Blog

Next ArticleEditing a PowerView RDLX file in Sharepoint 2010