Calculate Time Difference (hours) excluding Weekends & Holidays

[Anonymous]

All,

I'm having issue to calculate cycle-time in hours between two days also exclude Weekends & Holidays. 

Sample data as follow:

Data:

ID	Date	Start Time	End Time
1	3/14/2020	3/13/19 3:45 PM	3/14/19 4:46 PM
2	3/19/2020	3/6/19 2:00 PM	3/27/19 3:58 PM

Reference Table:

I have this Date reference table to show dates and weekdays:

t_Date_Key = GENERATE(

CALENDAR (DATE ( 2012, 01, 01 ), DATE ( 2021, 12, 31 ) ),

VAR FullDate = [Date]

VAR DayNumber = DAY( FullDate )

VAR DayofWeek= FORMAT(FullDate,"DDDD")

VAR MonthN = MONTH ( FullDate )

VAR Month = FORMAT(FullDate,"MMMM")

VAR MonthYYYYMM = FORMAT(FullDate,"YYYYMM")

VAR years = YEAR ( FullDate )

VAR WeekN = WEEKNUM(FullDate)

VAR DayofWeekN = IF(WEEKDAY(FullDate)=1,7,WEEKDAY(FullDate)-1)

VAR WeekStarting = FullDate - DayofWeekN+1

VAR WeekEnding = FullDate - DayofWeekN +7

VAR Qtr = "Q" & ROUNDUP(MONTH(FullDate)/3,0)

RETURN ROW (

"Day Number", DayNumber,

"Year", years,

"Quarter",Qtr,

"Month N", MonthN,

"Month YYYYMM", MonthYYYYMM,

"Month",Month,

"Month N Y",Month&" "&years,

"Week N Y", WeekN,

"Day of Week", DayofWeek,

"Day of Week N",DayofWeekN,

"Week Starting",WeekStarting,

"Week Ending",WeekEnding

))

I also have a holiday Reference table to show each holiday accros the year, linked with data table.

I have researched a lot but most of solutions is to count each workday as 1, sum workdays exclude weekends and holidays, which is not accurate in my case. Some of the request came in late afternoon, like 3:45PM, SME doesnt have time to work on that day but finish early next morning like 9AM, but result of this calculation give 2 workdays duration which make report to show SME missed SLA. 

Please advise any good solution to calculate duration in hours excluding holidays & weekends,

Thank you!

[v-easonf-msft]

Hi， @Anonymous 

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous ,please check the attached file can help

or this blog :https://curbal.com/blog/glossary/networkdays-dax