DATEADD FUNCTION RETURNS BLANK IF THE RESULT IS GREATER THAN THE INPUT YEAR

[Anonymous]

I have created calculated Table from an existing table loaded using Direct query, The Calculated table contain only one Column(Input_Date) with one Date  value, I Want to get the next 18 month(Output_date)  Date using the coloumn 'Input_Date'. I've tried with DATEADD function which return a date value ony if the output date year is same as input date year

If Input_Date =2018-Oct-10

DATEADD(Input_Date; +1 ; MONTH) =2018-Nov-10

DATEADD(Input_Date; +2 ; MONTH) =2018-Dec-10

DATEADD(Input_Date; +3 ; MONTH) =[BLANK]   (Expected Result =2019-Jan-10)

[traveler]

The design of this this method is werid - it is supposed to be a simple and straight forward method. what is the benefit to return blank if the result is greater than the input year or less than the input year?

i had similar issue when calucate the new column called startDateOfWeek,

startofweek = DATEADD(my_calendar[Date], -1*(WEEKDAY(my_calendar[Date],2) -1), DAY)

the DATEADD function returns blank for those dates that shift to a different year, for example, Jan 01, 2015 return blank instead of 12/29/2014.

my workaround is to use a simple culcuation like

startofweek = my_calendar[Date] - WEEKDAY(my_calendar[Date],2) +1

it works just fine.

[evandrocunico · Accepted solution]

Hi, 

https://community.powerbi.com/t5/Desktop/DateAdd-returns-blank-values/td-p/58742