calculate difference between two dates using DAX

Hey Anonymous ,

important: the relationship between the Calendar table and the Branch data table must be deleted.

I tackle these kinds of challenges by turning datetime columns into seconds, then doing the math needed based on the seconds, and finally formatting the result into some kind of readable format. This article might provide additional information: A duration is not the same as datetime! - Mincing Data - Gain Insight from Data (minceddata.info)

this measure

durtion without weekend and holidays = 
var durationInSeconds =
    SUMX(
        'Branch data'
        , var datetimeStart = CALCULATE( MAX( 'Branch data'[start date] ) )
        var dateStart = CONVERT( int( datetimeStart ) , DATETIME )
        var datetimeEnd = CALCULATE( MAX( 'Branch data'[end date] ) )
        var dateEnd = CONVERT( int( datetimeEnd ) , DATETIME )
        var numberWeekendHolidayInSeconds = 
            COUNTROWS(
                FILTER(
                    'calender'
                    , ( 'calender'[Date] >= dateStart && 'calender'[Date] <= dateEnd )
                    && ( 'calender'[IS_Workingday] = "Weekend" || 'calender'[IS_Holiday] = "Holiday" )
                )
            ) * 24 * 60 * 60 
        var dateDiffSeconds = DATEDIFF( datetimeStart , datetimeEnd , SECOND ) - numberWeekendHolidayInSeconds
        return
        dateDiffSeconds
    )

//formatting the resutlt
var noSecondS = 60
var noSecondsPerHour = noSecondS * 60
var noSecondsPerDay = noSecondsPerHour * 24
var _Days = TRUNC(DIVIDE(durationInSeconds , noSecondsPerDay ) )
var RemainingSecondsFromDay = MOD( durationInSeconds , noSecondsPerDay )
var _Hours = TRUNC(DIVIDE( RemainingSecondsFromDay , noSecondsPerHour ) )
var RemaingSecondsFromHour = MOD( RemainingSecondsFromDay , noSecondsPerHour )
var _Minutes = TRUNC(DIVIDE( RemaingSecondsFromHour , noSecondS ) )
var RemainingSecodndsFromHour = MOD( RemaingSecondsFromHour , noSecondS )
return
// IF( _Days = 0

    // , _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"

     _Days & " Day " & _Hours & " hr " & _Minutes & " mins " & RemainingSecodndsFromHour & " sec"

// )

returns what you are looking for:

Please check the expected result for the 1st row in your sample data, I'm almost sure that the result is correct.

Hopefully, this provides what you are looking for.

Regards,

Tom