Forum Discussion
DateAdd returns blank values
You are correct about DATEADD. From the article you linked: "The result table includes only dates that exist in the dates column."
I think DATEADD is typically used for time intelligence formulas within a measure, when you're only looking to shift your context to a different scope of dates already in your model.
If you wanted to add a single day to your Date column using a new column, you could always take the Date column and add 1:
Column = TestDates[Date] + 1
If you want to subtract a single year, I tried this at first:
MinusOneYear = DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date]))
That gives me an error ("An argument of function 'DATE' has the wrong data type or the result is too large or too small."), presumably for leap years, though I can't get rid of the error even accounting for leap years with an IF function. If I wrap all of it in IFERROR, it works, and 3/1/2015 is repeated because of the leap year, once for 3/1/2016 and once for 2/29/2016. This prevents the error, but I also don't see any blanks, so I'm not sure what caused the error:
MinusOneYear = IFERROR(DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date])), BLANK())
You might also try this in M in the Query Editor as a new column there:
Custom = Date.AddMonths([Date], -12)
That results in 2/28/2015 being repeated instead of 3/1/2015, so you'll have to be careful either way.
You are correct about DATEADD. From the article you linked: "The result table includes only dates that exist in the dates column."
I think DATEADD is typically used for time intelligence formulas within a measure, when you're only looking to shift your context to a different scope of dates already in your model.
If you wanted to add a single day to your Date column using a new column, you could always take the Date column and add 1:
Column = TestDates[Date] + 1
If you want to subtract a single year, I tried this at first:
MinusOneYear = DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date]))
That gives me an error ("An argument of function 'DATE' has the wrong data type or the result is too large or too small."), presumably for leap years, though I can't get rid of the error even accounting for leap years with an IF function. If I wrap all of it in IFERROR, it works, and 3/1/2015 is repeated because of the leap year, once for 3/1/2016 and once for 2/29/2016. This prevents the error, but I also don't see any blanks, so I'm not sure what caused the error:
MinusOneYear = IFERROR(DATE(YEAR(TableName[Date])-1, MONTH(TableName[Date]), DAY(TableName[Date])), BLANK())
You might also try this in M in the Query Editor as a new column there:
Custom = Date.AddMonths([Date], -12)
That results in 2/28/2015 being repeated instead of 3/1/2015, so you'll have to be careful either way.
Fantastic feedback, was hoping there would be a succinct alternative.
I was hoping to do any data transformation early-on, therefore I like the M option with the AddMonths function, I'll probably use the AddYears function.
The leap years/days might require some careful thought, but it may not impact the report we're trying to build.
This is great, thanks for your help.
Thanks for the reply with additional info! I looked for AddYears in the list of formulas and must have overlooked it, but it's more straightforward than months in this case.
It handles the leap year the same as AddMonths, and I think if I have to duplicate a date on either side, it's better to keep it in February. It makes more sense for month-over-month comparisons.
