PDA

View Full Version : [SOLVED] Power Query - Elapsed Months

JKwan
05-09-2017, 01:35 PM
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....

xld
05-12-2017, 04:26 PM
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
05-13-2017, 07:40 AM
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.

xld
05-13-2017, 10:51 AM
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
05-16-2017, 10:19 AM
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
10-17-2017, 05:12 AM
Thank y ou

TheTom
08-19-2019, 01:23 PM
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]))