Forum Discussion
DateAdd issue
Hello I have a set of dates that I want to add 1 years too, Here is the Dax
Iit works fine untill I get to 2023 See below
Any Ideas?
Hi Anonymous ,
Agree with edhans , please use EDATE() to add years.
Or you could use Date.AddYears() to a custom column in Power Query:
Date.AddYears([Date],1)And then change its type to Date. Below is the whole M syntax:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczBDYAwDATBXvxGOp+tJFBL5P7bIEiA4+c8ducUUyM66BLHKyo4Cp0fDSxyuKYaemJsl5URVxH/zp9nwtF2sKdOmETc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddYears([Date],1)), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}) in #"Changed Type1"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Don't use DATEADD to add dates. This is a time intelligence function that creates a table of dates. To add days, just use +1 for one day. To add years, use the EDATE function.
EDATE('Table'[Column1], 12)That will add 12 months to all dates and compensate for leap year.Just use increments of 12, So 12, 24, 36 for 1, 2, 3 years.
EDATE('Table'[Column1], 36)would add 3 years.
See this article, as well as a bit of info on EOMONTH. EDATE – DAX Guide
Both might be helpful for you here.
Hi Anonymous ,
Agree with edhans , please use EDATE() to add years.
Or you could use Date.AddYears() to a custom column in Power Query:
Date.AddYears([Date],1)And then change its type to Date. Below is the whole M syntax:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczBDYAwDATBXvxGOp+tJFBL5P7bIEiA4+c8ducUUyM66BLHKyo4Cp0fDSxyuKYaemJsl5URVxH/zp9nwtF2sKdOmETc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddYears([Date],1)), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}) in #"Changed Type1"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
