cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CarlsBerg999
Helper IV
Helper IV

Add months to date

Hi,

 

Im trying to add dates in a calculated column to a date. For example, I have date 1.5.2021 in Column A and 24 months in column B. I want the result to be 1.5.2023 in column C. However, for some reason DATEADD doesnt seem to work and DATE(year(),month(),day()) doesn't work when the value of months reaches above 12. 

 

Any ideas?

1 ACCEPTED SOLUTION

This can be quite tricky as the dateadd function requires a column reference and that column needs to have the date that would be the result of your addition. The simplest way to do this is to use the EDATE function so some thing like

 

NewDate = EDATE([Column A],[Column B])

 

# EDATE only works for months but that works fine in your case.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @CarlsBerg999 

Apart from the DAX EDATE([Column A],[Column B]) solution, if possible, you can also add a custom column in Power Query Editor.

= Date.AddMonths([Date],[Months])

041602.jpg

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

This can be quite tricky as the dateadd function requires a column reference and that column needs to have the date that would be the result of your addition. The simplest way to do this is to use the EDATE function so some thing like

 

NewDate = EDATE([Column A],[Column B])

 

# EDATE only works for months but that works fine in your case.

View solution in original post

selimovd
Super User
Super User

Hey @CarlsBerg999 ,

 

it should work with DATEADD. Try it like this as a calculated column:

MyNewDate = DATEADD('Date'[Date], 24, MONTH)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi, 

For some reason the calculated column returns blank when i do this. The date column is formatted as Date but for some reason refuses to accept any additions in months. I tried adding 2 months but it only worked for one row out of 250. Adding 3 months worked for 2 rows..

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.