Forum Discussion
Helper IDATEADD not accepting negative numbers
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
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.
5 Replies
Mo-PHATOKAY 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.
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
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)
