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.

  1. if today is May 31st then expected result is 28th May 2021
  2. if today is 1st june 2021 then expected result it 31st May 

 

appreciate any help here swat  mahoneypat edhans PhilipTreacy 

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)

7 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    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)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Fowmy  really appreciate your time for responding to my query .

    • chadmkelly's avatar
      chadmkelly
      Regular Visitor
      2 years ago

      Fowmy super helpful, just wondering about when Monday's are a Holiday - what logic would you use to get the last working day = to the Friday before the holiday? In this case Monday, 1/15/2024 was a Holiday, so I would want my last workday to show 1/12/2024. 
      thanks!

      • edhans's avatar
        edhans
        Community Champion
        2 years ago

        You'd need a table of holidays. My custom function above can be modified for this and with a bit of recursion, works. I keyed in the holidays as a list, but you could pull them in from a table and convert to a list for this purpose.

        (varDate as date) =>
let
    Source = 
        let
            varDayOfWeek = Date.DayOfWeek(varDate, Day.Monday),
            varHolidays = {#date(2024,1,1), #date(2024,1,15)},
            varPreviousWorkDay =
                if varDayOfWeek = 0 then Date.AddDays(varDate, -3)
                else if varDayOfWeek = 6 then Date.AddDays(varDate, -2)
                else Date.AddDays(varDate, -1)
        in
            if List.Contains(varHolidays, varPreviousWorkDay) 
            then fnPreviousWorkday(varPreviousWorkDay) 
            else varPreviousWorkDay
in
    Source

         

        You can see that both Jan 2 and Jan 16 should pick Jan 1 and 15, but that Monday is a holiday. So it goes back to Dec 29 and Jan 12 respectively.

         

  • 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])