Add Working Days 04-11-2024 14:02 PM

[Greg_Deckler]

Not sure why I never published this solution here to the QMG but better late than never I suppose. Couple measures to add working days to a date, one that accounts for holidays and one that doesn't. Also included in the PBIX is a column version (doesn't account for holidays) and a PQ version which also doesn't account for holidays. Could be easily adapted to do so.

Add Working Days Measure = 
    VAR __WorkDays = MAX('Table'[Work Days])
    VAR __Days = __WorkDays * 4
    VAR __StartDate = MAX('Table'[Start Date])
    VAR __Calendar = 
        FILTER(
            ADDCOLUMNS(
                CALENDAR( __StartDate + 1, __StartDate + __Days),
                "WeekDay",WEEKDAY([Date],2)
            ),
            [WeekDay] < 6
        )
    VAR __Table = 
        ADDCOLUMNS(
            __Calendar,
            "Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
        )
    VAR __Result = MAXX(FILTER(__Table, [Index] = __WorkDays),[Date])
RETURN
    __Result

Add Working Days Holidays Measure = 
    VAR __WorkDays = MAX('Table'[Work Days])
    VAR __Days = __WorkDays * 4
    VAR __StartDate = MAX('Table'[Start Date])
    VAR __Calendar = 
        EXCEPT(
            SELECTCOLUMNS(
                FILTER(
                    ADDCOLUMNS(
                        CALENDAR( __StartDate + 1, __StartDate + __Days),
                        "WeekDay",WEEKDAY([Date],2)
                    ),
                    [WeekDay] < 6
                ),
                "Date", [Date]
            ),
            'Holidays'
        )
    VAR __Table = 
        ADDCOLUMNS(
            __Calendar,
            "Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
        )
    VAR __Result = MAXX(FILTER(__Table, [Index] = __WorkDays),[Date])
RETURN
    __Result

eyJrIjoiOThmZGExYTUtNmU0ZC00MWZiLWEyOGMtNzU3MWY0NDBiY2YzIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9