Add working days to date

[cbtfs]

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?

[Greg_Deckler]

@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

[cbtfs]

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!

[Greg_Deckler · Accepted 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

[Bryanna]

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!