Forum Discussion

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

DateAdd does not work across year boundaries

Hi,

 

I have found that I cannot use a DateAdd to find a date outside of the current year.

 

Being the 31st Jan 2017 today, if I use (custom column)

 

DATEADD('Data'[Today].[Date],-3,month) It returns a blank value however

 

DATEADD('Data'[Today].[Date],-1,day) correctly returns yesterday.

 

Even DATEADD('Data'[Today].[Date],2,Year) returns a blank value.

 

Surely we should be able to dateadd across the boundary of the year.

 

Thanks

Kirsty

Data Modeling

3 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Mo-PHAT

     

    You need to create a Calendar table which contains the full calendar date if you want to use DATEADD Function.

    This blog should also be helpful to you.

     

     

    Best Regards,

    Herbert

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

      Thank you for helping me out.

       

      It seems to me that this is pretty ridiculous. I really hope someone fixes this