Power Query - Elapsed Months

[JKwan]

I am trying to calculate elapsed months within Power Query, but I am just not able to get it to work. I have a column named ApprovalDate (mm/dd/yyyy). What I want to do is get how many months had elapsed from today (run date). I've tried using DateDiff, but PQ just keep on saying not recognized DateDiff....

[Bob Phillips]

I feel there is probably a more elegant way, but ...


= Table.AddColumn(#"Changed Type", "ElapsedMonths", each (Date.Year(DateTime.LocalNow())*12+Date.Month(DateTime.LocalNow())) - (Date.Year([ApprovalDate])*12 + Date.Month([ApprovalDate])))

[JKwan]

xld, as always a big thank you. I will take a look on Monday. However, I don't know if you can answer the question.... I searched the web and people said use DateDiff, but when I try it out, it says not recognized?? This is more of a curiosity for me.

[Bob Phillips]

Power query has no DateDiff function that I am aware of. It does have Duration, but this only for days, hours. minutes,, seconds, not months or years. Or perhaps they were thinking of the DAX DATEDIFF function.

[JKwan]

you may be right with DAX vs Power Query.... I am so new with PQ.... I do remember when I googled, I do get mix results... However, does not matter, your formula did solve my problem. Thanks, xld.

[jeroenhollan]

Thank y ou

[TheTom]

Here is my solution for calculating months between two dates:


(((Date.Year([Finish Date]) - Date.Year([Start Date]))-1)*12)+(12-Date.Month([Start Date]))+(Date.Month([Finish Date]))