Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

The DAX DateAdd function and missing dates

with 10 comments

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)

image

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

image

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.

Written by Chris Webb

January 6, 2012 at 12:05 pm

Posted in DAX

10 Responses

Subscribe to comments with RSS.

  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.

    Jamiet

    January 6, 2012 at 12:20 pm

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

      Chris Webb

      January 6, 2012 at 12:52 pm

  2. Chris, good day!

    It’s may be an off-topic, but I try to find a possible desicion to this
    http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/baced302-2d0c-424f-874e-55d6187cb950

    100tsky

    January 7, 2012 at 3:35 am

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

    Jamal Bhatti

    January 7, 2012 at 5:36 pm

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

      Chris Webb

      January 7, 2012 at 8:47 pm

      • Excellent, thanks Chris, and thanks for accepting my linkedin invitation.

        Jamal

        Jamal Bhatti

        January 7, 2012 at 10:18 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,070 other followers

%d bloggers like this: