Forum Discussion
The DATEADD function returns wrong values.
Hello. I have a calendar table made with dax:
| calendar= Calendar(min(Date), max(Date)) |
The calendar starts on 07/04/1996 and ends on 06/11/1998
Now. I am using the function:
| ALL = Calculate(sum(column), dateadd(calendar[Date], -1, Year) |
And it returns wrong values, but when I alter the calendar table so that it starts on 01/01/1996 and ends on 12/31/1998, it returns the correct values. Why does this happen?
3 Replies
What are the wrong values?
The key point that catches a lot of people is that DATEADD can only return dates which already exist in the date table.
As the date table you have is quite limited, I suspect this is what's happening
What do you mean by? "DATEADD can only return dates which already exist in the date table."
Thanks
Well, DATEADD is a function that returns a date (or strictly a set of dates) but those dates must already exist in the date table defined in the model, for example, if you have a date table which has dates 15/03/1992 to 20/03/1992 i.e. just 6 dates and you use a formula like DATEADD(Calendar[Dates], 4, DAY) then the results would be like this:
15/03/1992 ->19/03/1992
16/03/1992 ->20/03/1992
17/03/1992 -> Should be 21/03/1992 but will return blank as 21/03/1992 is not in the original dates table
18/03/1992 -> Should be 22/03/1992 but will return blank as 22/03/1992 is not in the original dates table
and so on
