Calculate Duration Between Two Dates Based on Multiple Criteria (Activity Status, Holiday, Calendar)

Hi Taffalaffa ,

Try the following code for the columns (I added on the Base Data Table):

Planned Dur (D) =
SWITCH (
    TRUE (),
    'Base Data'[Holiday?] = "Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                'Date Table'[Date] <= 'Base Data'[Finish]
                    && 'Date Table'[Date] >= 'Base Data'[Start]
                    &&
                    VAR Calendar_type =
                        SWITCH (
                            'Base Data'[Calendar Type],
                            "5 Day", 'Date Table'[5 Day (H)],
                            "6 Day", 'Date Table'[6 Day (H)],
                            "7 Day", 'Date Table'[7 Day (H)]
                        )
                    RETURN
                        Calendar_type = "Workday"
            )
        ),
    'Base Data'[Holiday?] = "No Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                'Date Table'[Date] <= 'Base Data'[Finish]
                    && 'Date Table'[Date] >= 'Base Data'[Start]
                    &&
                    VAR Calendar_type =
                        SWITCH (
                            'Base Data'[Calendar Type],
                            "5 Day", 'Date Table'[5 Day (NH)],
                            "6 Day", 'Date Table'[6 Day (NH)],
                            "7 Day", 'Date Table'[7 Day (NH)]
                        )
                    RETURN
                        Calendar_type = "Workday"
            )
        )
)




Act Dur (D) =
SWITCH (
    TRUE (),
    'Base Data'[Activity Status] = "Not Started", 0,
    'Base Data'[Holiday?] = "Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                VAR Status_value =
                    SWITCH (
                        'Base Data'[Activity Status],
                        "Completed", 'Base Data'[Actual Finish],
                        "In Progress", 'Base Data'[Data Date]
                    )
                RETURN
                    'Date Table'[Date] <= Status_value
                        && 'Date Table'[Date] >= 'Base Data'[Actual Start]
                        &&
                        VAR Calendar_type =
                            SWITCH (
                                'Base Data'[Calendar Type],
                                "5 Day", 'Date Table'[5 Day (H)],
                                "6 Day", 'Date Table'[6 Day (H)],
                                "7 Day", 'Date Table'[7 Day (H)]
                            )
                        RETURN
                            Calendar_type = "Workday"
            )
        ),
    'Base Data'[Holiday?] = "No Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                VAR Status_value =
                    SWITCH (
                        'Base Data'[Activity Status],
                        "Completed", 'Base Data'[Actual Finish],
                        "In Progress", 'Base Data'[Data Date]
                    )
                RETURN
                    'Date Table'[Date] <= Status_value
                        && 'Date Table'[Date] >= 'Base Data'[Actual Start]
                        &&
                        VAR Calendar_type =
                            SWITCH (
                                'Base Data'[Calendar Type],
                                "5 Day", 'Date Table'[5 Day (NH)],
                                "6 Day", 'Date Table'[6 Day (NH)],
                                "7 Day", 'Date Table'[7 Day (NH)]
                            )
                        RETURN
                            Calendar_type = "Workday"
            )
        )
)



At Complete Duration =
SWITCH (
    'Base Data'[Activity Status],
    "Completed", 'Base Data'[Act Dur (D)],
    "In Progress", 'Base Data'[Remain Dur (D)] + 'Base Data'[Act Dur (D)],
    "Not Started", 'Base Data'[Remain Dur (D)]
)

Has you can see below result is matching the excel file:

PBIX file attach.

Oh my goodness this is perfect! I truly cannot thank you enough for your help! Thank you so very much!