Column to calculate Business hour between two dates excl holidays

[richardc1]

I aready have found a DAX script to determine business hours between two dates:

Busines Hours =

VAR _WorkStart =

    TIME( 9, 00, 0 )

VAR _WorkFinish =

    TIME( 17, 00, 0 )

VAR _1DayWorkingTime =

    DATEDIFF( _WorkStart, _WorkFinish, HOUR )

VAR _StartDate =

    'All Closed Tickets'[Opened Date Only] // Set the start Date column here

VAR _EndDate =

    'All Closed Tickets'[Closed Date Only] // Set the End Date column here

VAR _StartDateTime =

    'All Closed Tickets'[Begin Time] //  Set the start Time column here

VAR _EndDateTime =

    'All Closed Tickets'[End Time] //  Set the End Time column here

VAR _DaysBetweenStartFinish =

    ADDCOLUMNS( CALENDAR( _StartDate, _EndDate ), "DayN", WEEKDAY( [Date], 2 ) ) // You can use the Date table here

VAR _WorkingDaysBetweenStartFinish =

    COUNTX(FILTER(_DaysBetweenStartFinish,

            [Date] > _StartDate && [Date] < _EndDate && [DayN] < 6),

           [DayN]) // Sunday and Saturday are weekend days in this calculation

VAR _Day1WorkingHour =

    IF(WEEKDAY( _StartDate, 2 ) < 6,

        ( MAX( _WorkFinish, _StartDateTime ) - MAX( _StartDateTime, _WorkStart ) ) * 24,

        0)

VAR _LastDayWorkingHour =

    IF(WEEKDAY( _EndDate, 2 ) < 6,

        ( MIN( _WorkFinish, _EndDateTime ) - MIN( _EndDateTime, _WorkStart ) ) * 24,

        0)

VAR _Duration =

    IF(_StartDate = _EndDate&&WEEKDAY(_StartDate,2)<6,

        MAX(( MIN( _WorkFinish, _EndDateTime ) - MAX( _WorkStart, _StartDateTime ) ) * 24,

            0),

I also have a data table that incorporates public holidays, and a simply conditional column that determines whether each day is a working day. 

What I can't seem to do, is to use my existing date table that laready has this worked out, into the business hours code above.

I assume that it would repalce these lines:

VAR _DaysBetweenStartFinish =

    ADDCOLUMNS( CALENDAR( _StartDate, _EndDate ), "DayN", WEEKDAY( [Date], 2 ) ) // You can use the Date table here

VAR _WorkingDaysBetweenStartFinish =

    COUNTX(FILTER(_DaysBetweenStartFinish,

            [Date] > _StartDate && [Date] < _EndDate && [DayN] < 6),

           [DayN]) // Sunday and Saturday are weekend days in this calculation

This had syntax error:

VAR _WorkingDaysBetweenStartFinish =

    COUNTX(FILTER('Date Table',

            [Date] > _StartDate && [Date] < _EndDate && 'Date Table'[Working Day] = "Yes" ), 'Date Table'[Day Name]) // Count all working days

The "Opened Date Only and "Closed Date only" fields have inactive data connections to the 'Date Table' [Date] field which would necessitate a USERLATIONSHIP as well.

Sadly, I do not know how to incorporate my date table into this code successfully.

Any help greatly appreciated.

[amustafa · Accepted solution]

You can download my sample pbix file to see how to calculate business hours lapsed between two dates. I also have a public holiday calendar to account for business hours. The difference between your approach and mine is to do this in multiple steps instead of one go. (easier to understand the logic).

CRM Leads First Call Action Anaysis.pbix