March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
appreciate any help here @swat @mahoneypat @edhans @PhilipTreacy
Solved! Go to Solution.
@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)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Now, add a new column in your data table and use the formula =fnPreviousWorkday([NameOfDateColumn])
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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!
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}}),
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
55 | |
27 | |
17 | |
12 |