Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table (Time to Fill) with a date column (Position Opened) and want to create another column on the same table that displays 35 Business\Working Days from the "Position Opened" date, but after lots of searching, etc. cant find another thing. Was hoping there was a parameter to NETWORKDAYS where you could add days, but nope.
Thanks in advance.
Solved! Go to Solution.
@StuartSmith This depends on your logic. The way this is written, January 8th is working day +0, January 9th is working day +1 (from the Start Date). It seems you want to count the start date as the first working day. In that case, remove the +1 from the CALENDAR function so CALENDAR( [Start Date], [Start Date] + __Days)
@StuartSmith Here is a solution I came up with in DAX. PBIX is attached below signature.
Due Date =
VAR __Days = [Work Days] * 4
VAR __Calendar =
FILTER(
ADDCOLUMNS(
CALENDAR( [Start Date] + 1, [Start Date] + __Days),
"WeekDay",WEEKDAY([Date],2)
),
[WeekDay] < 6
)
VAR __Table =
ADDCOLUMNS(
__Calendar,
"Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
)
VAR __Result = MAXX(FILTER(__Table, [Index] = [Work Days]),[Date])
RETURN
__Result
And here is a PQ solution:
let WorkDayAdd = (Start as date, WorkDays as number) as date =>
let
Source =
List.Generate(
() => Date.AddDays(Start, WorkDays * 4),
each _ >= Date.AddDays(Start,1),
each Date.AddDays(_, -1 )
),
#"Converted to Table" =
Table.FromList(
Source,
Splitter.SplitByNothing(),
null, null, ExtraValues.Error
),
#"Added Custom" =
Table.AddColumn(
#"Converted to Table",
"WeekDay", each Date.DayOfWeek([Column1],Day.Monday)
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [WeekDay] < 5),
#"Sort Table" = Table.Sort(#"Filtered Rows", {"Column1"}),
#"Added Index" = Table.AddIndexColumn(#"Sort Table", "Index", 1, 1, Int64.Type),
#"Filtered Rows 1" = Table.SelectRows(#"Added Index", each [Index] = WorkDays),
#"Select Column" = Table.SelectColumns(#"Filtered Rows 1", "Column1"),
#"Result" = Record.Field(Table.Max(#"Select Column", "Column1"), "Column1")
in
#"Result"
in
WorkDayAdd
Thanks, I have it working, of sorts, but as an example, the start date is Mondays 8th Jan and 35 Business days would be Fri 23rd Feb, but your script is doing Mon 26th Feb.
Even you example file does the same...
What am i missing?
@StuartSmith This depends on your logic. The way this is written, January 8th is working day +0, January 9th is working day +1 (from the Start Date). It seems you want to count the start date as the first working day. In that case, remove the +1 from the CALENDAR function so CALENDAR( [Start Date], [Start Date] + __Days)
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |