Forum Discussion
Function - get previous workday date
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
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 SourceYou 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!
there more dynamic way. if you have continuous holidays like monday, tuesday also including weekends, so we need to show as friday as working day.
here code:// Merge the two tables on the 'Date' column to identify holidays
MergedTables = Table.NestedJoin(#"Converted to Table", "Date", Holiday, "Holiday Date", "HolidayInfo", JoinKind.LeftOuter),
// Expand the merged column to check if the date is a holiday
ExpandedTables = Table.ExpandTableColumn(MergedTables, "HolidayInfo", {"Holiday Date"}, {"Holiday Date"}),SortedTable = Table.Sort(ExpandedTables, {{"Date", Order.Ascending}}),
// Function to find the previous working day
FindPreviousWorkingDay = (date) as date =>
let
// Generate a list of previous dates up to 7 days back
PreviousDates = List.Transform({1..7}, each Date.AddDays(date, -_)),
// Filter out weekends and holidays to find the most recent working day
WorkingDays = List.Select(PreviousDates, each
not List.Contains(Holiday[Holiday Date], _) and // Not a holiday
Date.DayOfWeek(_, Day.Sunday) <> 0 and // Not a Sunday
Date.DayOfWeek(_, Day.Sunday) <> 6 // Not a Saturday
)
in
List.First(WorkingDays, date),
// Return the first valid working day or the original date if none found,
// Add column to compute the final adjusted date
AdjustedDates = Table.AddColumn(SortedTable, "End of Day", each let
currentDate = [Date],
dayOfWeek = Date.DayOfWeek(currentDate, Day.Sunday), // Day of the week (0=Sunday, 6=Saturday)
isWeekend = (dayOfWeek = 6 or dayOfWeek = 0), // Check if Saturday (6) or Sunday (0)
isHoliday = [Holiday Date] <> null, // Check if the date is a holiday
// Determine the adjusted date
adjustedDate =
if isWeekend or isHoliday then
FindPreviousWorkingDay(currentDate) // Adjust weekends and holidays to the previous working day
else
currentDate // No adjustment needed for regular weekdays
in
adjustedDate),
#"Transform columns" = Table.TransformColumnTypes(AdjustedDates, {{"Business Day", type date}}),
