Response Time and Response Day excluding holidays and weekends DAX

[corpsikumar]

Hi Power Users,

I'm trying to create a calculation for finding total numbers of responses by ID for reponse time (Working Hours) and response time(Working Days) and want to exclude Holidays and weekends. The Working hours are between 6AM-5PM (Monday-Friday). I have two date/time fields(StartDate , End Date). EndDate might have blanks.

Below is the sample data Looks like.

I have excel formulas and trying to convert them into DAX. 

Response Time (working hours)=IF(C2="","",(NETWORKDAYS.INTL(B2,C2,1,'Data Sheet'!A$47:A$89)-1)*("17:00"-"6:00")+IF(NETWORKDAYS.INTL(C2,C2,1,'Data Sheet'!A$47:A$89),MEDIAN(MOD(C2,1),"6:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(B2,B2,1,'Data Sheet'!A$47:A$89)*MOD(J2,1),"6:00","17:00"))

Response Time (working days)=IF(C2="","",((INT(D2)*24+HOUR(D2)+ROUND(MINUTE(D2)/60,2)))/11)

'Data Sheet'!A$47:A$89 is the holidays list of dates.

Please help. 

[v-yanjiang-msft · Accepted solution]

Hi @corpsikumar ,

According to your description, here's my solution.

1. Create a date table, don't make relationship between this table and the fact table.

Date =
ADDCOLUMNS ( CALENDARAUTO (), "Weekday", WEEKDAY ( [Date], 2 ) )

2. In the fact table, create two calculated columns.

Response time (Working Hours) =
VAR _M =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        DATESBETWEEN ( 'Date'[Date], 'Table'[StartDate] + 1, 'Table'[ClosedDate] - 1 ),
        'Date'[Weekday] <> 6
            && 'Date'[Weekday] <> 7,
        ALL ( 'Table' )
    ) * 11 * 60
VAR _S =
    IF (
        WEEKDAY ( 'Table'[StartDate], 2 ) IN { 6, 7 },
        0,
        IF (
            HOUR ( 'Table'[StartDate] ) < 6,
            11 * 60,
            DATEDIFF (
                'Table'[StartDate],
                DATE ( YEAR ( 'Table'[StartDate] ), MONTH ( 'Table'[StartDate] ), DAY ( 'Table'[StartDate] ) )
                    + TIME ( 17, 00, 00 ),
                MINUTE
            )
        )
    )
VAR _C =
    IF (
        WEEKDAY ( 'Table'[ClosedDate], 2 ) IN { 6, 7 },
        0,
        IF (
            HOUR ( 'Table'[ClosedDate] ) >= 17,
            11,
            DATEDIFF (
                DATE ( YEAR ( 'Table'[ClosedDate] ), MONTH ( 'Table'[ClosedDate] ), DAY ( 'Table'[ClosedDate] ) )
                    + TIME ( 6, 00, 00 ),
                'Table'[ClosedDate],
                MINUTE
            )
        )
    )
VAR _Minute =
    IF (
        ISBLANK ( 'Table'[ClosedDate] ),
        BLANK (),
        _M
            + IF (
                EDATE ( 'Table'[StartDate], 0 ) = EDATE ( 'Table'[ClosedDate], 0 )
                    && NOT ( WEEKDAY ( 'Table'[StartDate], 2 ) IN { 6, 7 } ),
                DATEDIFF ( 'Table'[StartDate], 'Table'[ClosedDate], MINUTE ),
                _S + _C
            )
    )
RETURN
    IF (
        _Minute = BLANK (),
        BLANK (),
        INT ( _Minute / 60 ) & ":"
            & MOD ( _Minute, 60 ) & ":" & "00"
    )

Response time(Working Days) =
VAR _M =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        DATESBETWEEN ( 'Date'[Date], 'Table'[StartDate] + 1, 'Table'[ClosedDate] - 1 ),
        'Date'[Weekday] <> 6
            && 'Date'[Weekday] <> 7,
        ALL ( 'Table' )
    ) * 11 * 60
VAR _S =
    IF (
        WEEKDAY ( 'Table'[StartDate], 2 ) IN { 6, 7 },
        0,
        IF (
            HOUR ( 'Table'[StartDate] ) < 6,
            11 * 60,
            DATEDIFF (
                'Table'[StartDate],
                DATE ( YEAR ( 'Table'[StartDate] ), MONTH ( 'Table'[StartDate] ), DAY ( 'Table'[StartDate] ) )
                    + TIME ( 17, 00, 00 ),
                MINUTE
            )
        )
    )
VAR _C =
    IF (
        WEEKDAY ( 'Table'[ClosedDate], 2 ) IN { 6, 7 },
        0,
        IF (
            HOUR ( 'Table'[ClosedDate] ) >= 17,
            11,
            DATEDIFF (
                DATE ( YEAR ( 'Table'[ClosedDate] ), MONTH ( 'Table'[ClosedDate] ), DAY ( 'Table'[ClosedDate] ) )
                    + TIME ( 6, 00, 00 ),
                'Table'[ClosedDate],
                MINUTE
            )
        )
    )
VAR _Minute =
    IF (
        ISBLANK ( 'Table'[ClosedDate] ),
        BLANK (),
        _M
            + IF (
                EDATE ( 'Table'[StartDate], 0 ) = EDATE ( 'Table'[ClosedDate], 0 )
                    && NOT ( WEEKDAY ( 'Table'[StartDate], 2 ) IN { 6, 7 } ),
                DATEDIFF ( 'Table'[StartDate], 'Table'[ClosedDate], MINUTE ),
                _S + _C
            )
    )
RETURN
    DIVIDE ( _Minute, 11 * 60 )

Get the result. In the ID3, your original date is 2022/2/26 which is a saturday, as per your requirement the result should be empty. I change it to 2022/2/25 and get the result. In ID5, 2022/3/26 is a saturday and 2022/3/27 is a sunday, the output in your snapshot is also incorrect. May be the definition of Saturday and Sunday is different.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[corpsikumar]

@v-yanjiang-msft It worked like a charm. Thank you so much

[amitchandak]

@corpsikumar , DAX also networkdays

https://dax.guide/networkdays/

Or refer

https://exceleratorbi.com.au/calculating-business-hours-using-dax/