Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Helper V
5 years ago
Solved

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?

Need Help
  • MikeJohnsonZA's avatar
    MikeJohnsonZA
    5 years ago

    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.

5 Replies

  • MikeJohnsonZA's avatar
    MikeJohnsonZA
    Responsive Resident
    5 years ago

    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.

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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
     
    • CarlsBerg999's avatar
      CarlsBerg999
      Helper V
      5 years ago

      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..

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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])

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

  • Jon_vB's avatar
    Jon_vB
    Advocate II
    3 years ago

    For me, I was trying to add 1 to the month inside my Date table definition - so - the fact that DateADD requires a column was annoying, and I couldn't reference the table I was trying to build.  My solution was to use the EOMONTH function (EOMONTH - DAX Guide).

     

    (note - this reply was edited - my former answer didn't take into account December to January transition)

     

    "YY-MMM Or Current"
    ,IF( TODAY() >= DATE( YEAR([Date]), MONTH([Date]), 1) && TODAY() <= EOMONTH([Date],0)
         , "Current Month"
         , FORMAT(DATE(YEAR([Date]),MONTH([Date]) ,1),"YYYY-MMM")
       )