Forum Discussion

itsmeanuj's avatar
itsmeanuj
Icon for Helper IV rankHelper 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 ...
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)))
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper 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
Icon for Helper IV rankHelper 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
    Icon for Super User rankSuper 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)))