Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am trying to create a custom column that will calculate a "due date". I have a start date column and a column with the number of working days that I want to add to the start date to produce the due date.
Any thoughts?
Solved! Go to Solution.
@cbtfs Maybe a custom function like this:
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")
in
Record.Field(Table.Max(#"Select Column", "Column1"), "Column1")
in
WorkDayAdd
@cbtfs I realize that this is not Power Query but perhaps this DAX solution will allow you to create an equivalent in PQ.
Due Date =
VAR __Days = [Work Days] * 2
VAR __Calendar =
FILTER(
ADDCOLUMNS(
CALENDAR( [Start Date], [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
Hi Greg,
Thanks for the info, but I am specifically looking for a solution within Power Query. I don't have any experience with DAX unfortunately and am looking to add this feature onto an existing power query.
Thanks!
@cbtfs Maybe a custom function like this:
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")
in
Record.Field(Table.Max(#"Select Column", "Column1"), "Column1")
in
WorkDayAdd
Hi! Came accross this post and this worked in power query! What if i also dont want it to count a holiday along with weekends. How can i adjust the custom function to also disregard holidays?
Thanks!