DATEADD not accepting negative numbers

[Mo-PHAT]

Hi,

I am trying to create a column which displays the date 3 months before today.

My DAX query is simple - 3 months ago = DATEADD('Datasource'[Today].[Date],-3,MONTH)

I created a column with todays date in it so it can be used in the Dateadd.

However it returns a blank field.

If I change it to just a regular 3 without the negative sign, it works and gives a date 3 months from today.

Why won't it accept -3??

Thanks in advance

[RonaldoDegazon]

Hi, 

After hours of trial and error I solved this problem by marking the table to which I made reference in the DATEADD function as my Date Table. 

See instructions on how to mark a table as a Date Table below:

Enabling Time Intelligence :: Analytics with Power BI Desktop (bizdata.com.au)

[Mo-PHAT · Accepted solution]

OKAY one of my super smart colleagues found that if we are trying to pass over the start of the year it will not work.

Today being the 31 Jan 2017, any DATEADD with a negative number that tries to go before the start of the year just returns a blank column.

Surely this is a major flaw!?

I'll  relog this as an issue.

[boot_heel]

Has this been logged as a bug? It's almost 4 years after first surfacing this issue here and I'm now experiencing it. If DATEADD() was never intended to compute across year end/start dates then it's extremely inconvenient, but I can probably find a workaround. If It is not computing as intended then this is a bug and needs to be resolved.

[Mo-PHAT]

 Hi,

Casting my mind back I think this issue was occurring because I was trying to use date fields in the fact table rather than a date dimension, or something along those lines. perhaps even not having time intelligence on or off.  I don't get this issue anymore but I also am more likely to create the time difference calculation in Power query.

Ta

K

[boot_heel]

I don't think it's necessary to continue reviving this conversation, but specifically in the context of finding a date that is x number of months before or after another date the EDATE() function appears to be the best choice versus DATEADD() which is failing to display the correct behavior over the end/beginning of the calendar year.