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....
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])))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
Thank y ou
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]))
Posting Permissions
Reply With Quote
