Now that my book is wrapping up I finally have a bit more time to do some blogging. I have quite the backlog :). A quick one today. I got this question today:
Why does my function DATEADD not return what I expect? It gives me empty results on some row??
So what is happening here? He is using the DATEADD function to move back to the value of Table1[Date] 14 days previous to the value for the current row. Remember DATEADD is a time intelligence function, those have special rules, one of them is that the the dates need to be in a contiguous date range, there cannot be a missing dates in the middle. If you do miss dates you will get unexpected results, in the case above the date “12/7/2013” did not exist as a value in the Table1[Date] column, thus will return empty results.
What do you need to do? two options:
1 Create a separate date table containing a continuous date range for all values you want to report on, this will solve the “missing values”
2 Don’t use time intelligence functions, in this case you could have done a simple “=Table1[Date]-14”. That DAX expression simply uses the value for each row in the table to create a new date value. It doesn’t need to lookup a value in the column, it can work off its own value.