Forum Discussion

Mo-PHAT's avatar
Mo-PHAT
Icon for Helper I rankHelper I
9 years ago
Solved

DATEADD 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...
Need Help
  • Mo-PHAT's avatar
    Mo-PHAT
    9 years ago

    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's avatar
boot_heel
Frequent Visitor
6 years ago

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's avatar
Mo-PHAT
Icon for Helper I rankHelper I
6 years ago

 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's avatar
    boot_heel
    Frequent Visitor
    6 years ago

    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.