The DAX DateAdd function and missing dates

Reposted from Chris Webb's blog with the author's permission.

A little while ago I was working with a date column in a Tabular model and wanted to create a calculated column that returned a date 100 days after the original date. Without thinking too much about it, I started off by using the DAX DateAdd() function – after all, I wanted to add some days to a date so it seemed like the obvious function to use. You can recreate this scenario in Adventure Works by importing the DimDate table into a new model in SSDT (SQL Server Data Tools, not BIDS – don’t forget to use the new name!) and then adding a new calculated column with the following expression:

=DATEADD(DimDate[FullDateAlternateKey], 100, day)

What I noticed was that it only worked for some dates and not all; for example, in the screenshot above it works up to September 22nd and not afterwards. This threw me for a few minutes, and then I realised what the problem was. In fact, the reason is mentioned (though maybe not explained as well as it should be) in the documentation for the DateAdd function:

The result table includes only dates that exist in the dates column.

The reason why DateAdd was returning a value for September 22nd 2006 was that the value December 31st 2006 existed in the FullDateAlternateKey column; it was not returning anything for September 23rd 2006 because the date January 1st 2007 did not exist in FullDateAlternateKey.

In fact there was an even easier way to do what I wanted to do, since the DAX date type is really a decimal value where the integer portion is the number of days – I could just add 100 to the date, as follows:

=DimDate[FullDateAlternateKey]+100

So, not a great discovery and certainly nothing that wasn’t known about before, but I thought it was worth mentioning because I’m sure other people (as here, for example) will fall into the same trap as me.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .