Get 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 have a "Employee" column with a start date to which i need to add 3 working days to each date. Also I have a Holiday table with Holiday dates.
What i have achieved so far, with an example is:
If the given date is August 13, 2024 (Tuesday). I add 3 days to it which result as August 16, 2024.
Then, merged the employee and holiday table and implemented this condition: If August 16, 2024 (Friday) is a Holiday. Then add one day to it. Which is a August 17, 2024 Saturday. Then i add 2 more days to it to make it the August 19, 2024 (Monday).
I need help with this situation:
Let's say August 19, 2024 (Monday) is also a holiday, then add one more day to it and make it a Tuesday (the next working day). So basically, i need to find the 4th working day from the given date. Thanks.
Solved! Go to Solution.
Hi @meghansh, check this
Lets imagine you have holidays these 4 days in January:
For 4th of January next 3rd working date will be 11th because:
5th = holiday
6th = weekend
7th = weekend
8th = holiday
9th - 11th are working days and you want 3rd working day so 11th
let
EmployeeTable = Table.FromList(List.Dates(#date(2024,1,1), 31, #duration(1,0,0,0)), (x)=> {x}, type table[Start Date=date]),
HolidayTable = Table.FromList({#date(2024,1,1), #date(2024,1,5), #date(2024,1,8), #date(2024,1,25)}, (x)=> {x}, type table[Date=date]),
StepBack = EmployeeTable,
InsertedDayName = Table.AddColumn(EmployeeTable, "Day Name", each Date.DayOfWeekName([Start Date], "en-US"), type text),
Ad_NextWorkingDate = Table.AddColumn(InsertedDayName, "Next Working Date", each
[ a = List.Dates([Start Date], 10, #duration(1,0,0,0)),
b = List.Select(List.Skip(a), (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday)) and not List.Contains(List.Buffer(HolidayTable[Date]), x)),
c = b{2}?
][c], type date)
in
Ad_NextWorkingDate
Hey,
A possible solution is the function i made.
1. It creates a small table of the 8 days after the input
2. it will first filter out weekend days and secondly dates that appear in the HolidayDates list.
3. It then returns the third remaining date in the table.
func_AddDate =
(vDate as date) =>
let
Dates = Table.FromList(List.Dates(vDate + #duration(1,0,0,0), 8, #duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
changetypes = Table.TransformColumnTypes(Dates, {"Date", type date}),
add_weekday = Table.AddColumn(changetypes, "Weekday", each Date.DayOfWeek([Date], Day.Monday)),
removeweekends = Table.SelectRows(add_weekday, each ([Weekday] <> 5 and [Weekday] <> 6) and (List.Contains(HolidayTable[Date], [Date]) = false)),
add_index = Table.AddIndexColumn(removeweekends, "Index", 1, 1),
selectDate = Table.SelectRows(add_index, each ([Index] = 3))
in
selectDate[Date]{0}
in the main query add:
Table.AddColumn(PREVSTEP, "New Date", each func_AddDate(old_date))
Hey,
A possible solution is the function i made.
1. It creates a small table of the 8 days after the input
2. it will first filter out weekend days and secondly dates that appear in the HolidayDates list.
3. It then returns the third remaining date in the table.
func_AddDate =
(vDate as date) =>
let
Dates = Table.FromList(List.Dates(vDate + #duration(1,0,0,0), 8, #duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
changetypes = Table.TransformColumnTypes(Dates, {"Date", type date}),
add_weekday = Table.AddColumn(changetypes, "Weekday", each Date.DayOfWeek([Date], Day.Monday)),
removeweekends = Table.SelectRows(add_weekday, each ([Weekday] <> 5 and [Weekday] <> 6) and (List.Contains(HolidayTable[Date], [Date]) = false)),
add_index = Table.AddIndexColumn(removeweekends, "Index", 1, 1),
selectDate = Table.SelectRows(add_index, each ([Index] = 3))
in
selectDate[Date]{0}
in the main query add:
Table.AddColumn(PREVSTEP, "New Date", each func_AddDate(old_date))
Hi @meghansh, check this
Lets imagine you have holidays these 4 days in January:
For 4th of January next 3rd working date will be 11th because:
5th = holiday
6th = weekend
7th = weekend
8th = holiday
9th - 11th are working days and you want 3rd working day so 11th
let
EmployeeTable = Table.FromList(List.Dates(#date(2024,1,1), 31, #duration(1,0,0,0)), (x)=> {x}, type table[Start Date=date]),
HolidayTable = Table.FromList({#date(2024,1,1), #date(2024,1,5), #date(2024,1,8), #date(2024,1,25)}, (x)=> {x}, type table[Date=date]),
StepBack = EmployeeTable,
InsertedDayName = Table.AddColumn(EmployeeTable, "Day Name", each Date.DayOfWeekName([Start Date], "en-US"), type text),
Ad_NextWorkingDate = Table.AddColumn(InsertedDayName, "Next Working Date", each
[ a = List.Dates([Start Date], 10, #duration(1,0,0,0)),
b = List.Select(List.Skip(a), (x)=> not List.Contains({5, 6}, Date.DayOfWeek(x, Day.Monday)) and not List.Contains(List.Buffer(HolidayTable[Date]), x)),
c = b{2}?
][c], type date)
in
Ad_NextWorkingDate
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
61 | |
19 | |
17 | |
13 |