Adding X Business\Working Days to another date.

[StuartSmith]

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.

[Greg_Deckler]

@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

[StuartSmith]

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.  

35 Business Days From Position Opened =

    VAR DaysToFill = 35

    VAR __Days = DaysToFill * 4

    VAR __Calendar =

        FILTER(

            ADDCOLUMNS(

                CALENDAR( [Position Open Date] + 1, [Position Open 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] = DaysToFill),[Date])

RETURN

    __Result

[StuartSmith]

Even you example file does the same...

What am i missing?

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