The DAX DateAdd function and missing dates

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:



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.

13 responses

  1. I haven’t done any work at all using DAX so forgive my being a little green here, but can you explain the justification for:
    “The result table includes only dates that exist in the dates column.”
    I’m sure there is a good reason for this but it seems like a vey strange limitation to me.

    • There is a good reason, and it’s one of those things that I understand but can’t necessarily explain very well. Let me try though. You usually use the DateAdd() in time intelligence calculations in DAX, and when you do that you take a column of values, shift the dates somehow and then use these shifted dates as a filter parameter inside the Calculate() function. This only works if your shifted column of dates only contains values that exist inside the original column.

  2. Hi Chris,

    Thanks for your posts – they are most useful.

    I was wondering if you could post on table relationships through SSAS. In particular possible fixes for database join issue:

    Analysis Services Error: ‘table that is required for a join cannot be reached’

    In SSAS I have three dimensions one of which relates to a fact table. When I process the dimension with attributes from each of the other two dimensions I get the above error.

    There is definitely a join, between the main dimension and the other two dimensions. The primary key in the same dimension i.e book also relates to the other book foreign keys in the other dimensions.

    If I script with two joins it works but SSAS doesn’t seem to pick this up.

    I have looked at previous posts and tried their recomendation but they are not appropriate for my issue it seems.

    • This error usually occurs when you have multiple relational tables used in a single SSAS dimension. You can solve it by creating a single view on top of all of these tables, and building your SSAS dimension from that view instead.

  3. I was wondering, why am I getting blank values for my date calculations. This article helped me figure out the issue. Thank you for this article.

  4. Hey guys, I need to remove a month to a date columns and don’t want to use a number of days as they would differ from one month to another. Any suggestion?

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: