Consulting

Results 1 to 6 of 6

Thread: Power Query - Elapsed Months

  1. #1

    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 xld's Avatar
    Joined
    Apr 2005
    Posts
    24,836
    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]))) 
    
    
    Formatting tags added by mark007
    ____________________________________________
    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
    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 xld's Avatar
    Joined
    Apr 2005
    Posts
    24,836
    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
    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

Posting Permissions

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