Power Query M function to add days to a date

You have a parameter like `today` and you want to easily add and substrate days from that... like yesterday is `days_ago(1)`

me@jaykilleen.com wrote this 10 months ago and it was last updated 10 months ago.


← Back to the Posts

Hey if you like my post about how to get todays date in power query m then your like to also want something like a function that allows you to easily add or substract days ago.

In some languages like Ruby this is done like today - 1.day or today - 1.days.ago so I have created a function in Power Query M that is just as elegant as this.

So slap this function in your Power Query and pair it with that todays date parameter.

DAYSAGO.pq

let
   days_ago = (n) =>
      let
          date = Date.AddDays(today, -n)
      in
       date
in
   days_ago

Now you can do stuff like

Table.AddColumn(
  last_step, "in_last_7_days?", 
  each 
    if Date.From([invoiced_at]) >= DAYSAGO(-7) then 
      true 
    else 
      false
)

You can also use this to add days by passing a positive value to the function like DAYSAGO(today, 1) but I'd recommend just having another function called DAYSAHEAD or something else intuitive so your coding feels more natural and less like speaking in double negatives.

Similar