Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Function - get previous workday date

I am looking for some power query gurus help here , as am totally unfamiliar with M language .    i would need to create a function to get my previous working day date .  E.g. if today is May 31...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Add the following code as a new custom column: 

    let
  today = Date.From(DateTime.LocalNow()), 
  d     = Date.DayOfWeek(today, Day.Monday)
in
  if d = 6 then
    Date.AddDays(today, - 2)
  else if d = 0 then
    Date.AddDays(today, - 3)
  else
    Date.AddDays(today, - 1)
edhans's avatar
edhans
Community Champion
5 years ago

Here is a cutom function to do that Anonymous 

(varDate as date) =>
let
    Source = 
        let
            varDayOfWeek = Date.DayOfWeek(varDate, Day.Monday)
        in
        if varDayOfWeek = 0 then Date.AddDays(varDate, -3)
        else if varDayOfWeek = 6 then Date.AddDays(varDate, -2)
        else Date.AddDays(varDate, -1)
in
    Source

 

  1. Create a new blank query
  2. In the advanced editor, remove ALL of the code
  3. Paste the code above into the advanced editor and press Done.
  4. Rename it fnPreviousWorkDay (instead of Query1 or whatever it was called.

Now, add a new column in your data table and use the formula =fnPreviousWorkday([NameOfDateColumn])