Consulting

Results 1 to 7 of 7

Thread: Power Query - Elapsed Months

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location

    Power Query - Elapsed Months

    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....

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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

  3. #3
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    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.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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

  5. #5
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    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.

  6. #6
    Thank y ou

  7. #7
    VBAX Newbie
    Joined
    Aug 2019
    Posts
    1
    Location
    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

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •