Dateadd function not working

[ysherriff]

Hi all,

I am trying to do a simple calculation to get the previous year utilizing dateadd. In Image 1, when I use previous year, the output is correct. When I tried to use Dateadd, the output shows blank, see image 2. I think  Dateadd doesn't like my date table but I don't understand where the issue lies. Any help would be appreciated.

Image 1

Image 2

[PaulDBrown]

DATEADD should work fine.

[ysherriff]

I will try to do a workaround since my model doesn't fit DateAdd requirements.

Thanks

[PaulDBrown]

If your model contains a date table with continuous dates covering the range of dates in the model, the date table date field is linked to the date field in your fact model in a one-to-many single direction relationship and you use the date table field in your visuals, DATEADD should work ok.

[ysherriff]

Attach is the data model

[PaulDBrown · Accepted solution]

PREVIOUSYEAR returns the values covering all dates in the previous year, while DATEADD as you have it returns the values for the same date in the previous year.
Are there any values for the previous year's dates where it is now showing blank?

Also, does the date table contain continuous dates covering the whole range of dates in the model?

[ysherriff]

Hi Paul,

To answer your last question first, the date table is continuous and covers the whole range of dates in the model, meaning the start and end date of the date table matches the start and end date of the model

As it relates to the first question, there are some date gaps within the model date. So it is not all continuous in the model. The data from the model is extracted from a user database so there is nothing I can do on that end.

Is there a workaround. All I am trying to do is get last year and last quarter using dateaddd in case i need to extract last 2 quarters or years. Dateadd is just more versatile.

I hope that helps.