I've done something similar before, but with days instead of hours. The approach I used was based on a MasterCalendar and I believe the same principle applies here.
The idea is:
1. Create a calendar with hour-level granularity, generating one row per hour between the min and max dates in your dataset, with a flag to identify business hours:
If(WeekDay([%DateHourKey]) < 5 // Monday to Friday
AND Hour([%DateHourKey]) >= 8
AND Hour([%DateHourKey]) < 18, 1, 0) as IsBusinessHour
2. Cross your data with this calendar filtering where the hour falls between the opened and resolved dates
3. Count the rows where IsBusinessHour = 1 that gives you the total business hours
No need for IntervalMatch the calendar handles the counting naturally, the same way a day-level calendar counts working days.
I've done something similar before, but with days instead of hours. The approach I used was based on a MasterCalendar and I believe the same principle applies here.
The idea is:
1. Create a calendar with hour-level granularity, generating one row per hour between the min and max dates in your dataset, with a flag to identify business hours:
If(WeekDay([%DateHourKey]) < 5 // Monday to Friday
AND Hour([%DateHourKey]) >= 8
AND Hour([%DateHourKey]) < 18, 1, 0) as IsBusinessHour
2. Cross your data with this calendar filtering where the hour falls between the opened and resolved dates
3. Count the rows where IsBusinessHour = 1 that gives you the total business hours
No need for IntervalMatch the calendar handles the counting naturally, the same way a day-level calendar counts working days.
