Forum Discussion
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...
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)
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!
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.
- thanks for this, very helpful!