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.
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
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.
Oh right, clear as mud 🙂
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
I don’t understand exactly what you’re asking for in this thread – can you provide more details?
may I send you a file?
Yes, you can find my contact details at http://www.crossjoin.co.uk
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.
Excellent, thanks Chris, and thanks for accepting my linkedin invitation.
Jamal
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.
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?
Hey guys, Nice workaround but doesn’t apply if I’m trying to offset months or years instead of days. Any suggestion?
Thanks for this. I had been trying to figure this out for over 2 hours now….
Thanks a lot for this! I used dateadd for forecasting in SQL to create a report in SSRS and it works perfectly there. I was going Bonkers as to why PowerBI is giving blank columns. PowerBI sure ruined it. Why do you mess with our brains POWERBI!!!!