Forum Discussion

itsmeanuj's avatar
itsmeanuj
Helper IV
2 years ago
Solved

calculate months difference between 2 dates.

Hi Guys,

 

Can someone please help me find the difference in months between 2 dates (basically just 1st of every month) in Transform data (Power query) I want to create a new column with the month difference. e.g. 

 

date1 = 11/1/2022

date2 = 12/1/2023

 

expected result - Month_diff =  13

 

Thanks,

Anuj

Need Help
Show and Tell
Tips and Tricks
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    try this

    Try List.Count (List.Generate (()=> [date1] ,
  (x)=> x<= [date2],
  (x)=> Date.AddMonths(x,1))) otherwise List.Count (List.Generate (()=> [date2] ,
  (x)=> x<= [date1],
  (x)=> Date.AddMonths(x,1)))

4 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this code

    List.Count (List.Generate (()=> [date1] ,
  (x)=> x<= [date2],
  (x)=> Date.AddMonths(x,1)))

    • itsmeanuj's avatar
      itsmeanuj
      Helper IV
      2 years ago

      Thanks! it is giving me an error for the cases where date2 is smaller than date1. we need a formula which would work in both cases. 

      • Ahmedx's avatar
        Ahmedx
        Super User
        2 years ago

        try this

        Try List.Count (List.Generate (()=> [date1] ,
  (x)=> x<= [date2],
  (x)=> Date.AddMonths(x,1))) otherwise List.Count (List.Generate (()=> [date2] ,
  (x)=> x<= [date1],
  (x)=> Date.AddMonths(x,1)))
  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    You can use Datediff dax function with month as interval argument

     

    • The DATEDIFF function with MONTH as the interval argument, e.g. DATEDIFF (StartDate, EndDate, MONTH)

    Please accept as solution if it helps