How to limit duration to a given time range

[TenguMan]

Hello,

I have a report that deals with customer service case data and how long a given case takes. I have been asked to calculate the duration of these cases such that it only counts the time that falls within specified business hours (in this case, 8:00 AM to 8:00 PM). A sample table is below:

Case Number	Case Opened	Case Closed	Duration (Hours)
CS0000001	8:00:00 AM	8:00:00 PM	12
CS0000002	8:00:00 AM	7:00:00 AM	23
CS0000003	7:00:00 PM	7:59:00 AM	12.98

After this calculation, the table should look like this:

Case Number	Case Opened	Case Closed	Duration (Hours)
CS0000001	8:00:00 AM	8:00:00 PM	12
CS0000002	8:00:00 AM	7:00:00 AM	12
CS0000003	7:00:00 PM	7:59:00 AM	1

How can I go about doing this in Power BI? Any assistance is greatly appreciated. Thank you!

[halfglassdarkly]

How are you accounting for cases that span multiple days (assuming that is possible)?

If you have a date/time value you can use the DATEDIFF function to calculate hours between date times. E.g.

DATEDIFF([Start Date/Time],[End Date/Time],HOUR)

If you have Date and Time as seperate columns you can add them together like so:

DATEDIFF([Start Date]+[Start Time],[End Date]+[End Time],HOUR)

If you only care about comparing time and not date (if case open/close are always the same date) you could use an arbitrary date e.g.

DATEDIFF(DATE(2024,01,01)+[Start Time],DATE(2024,01,01)+[End Time],HOUR)

UPDATE: apologies, I completely missed the part where you said you needed to calculate work hours. Greg's solution looks more promising for that.

[Greg_Deckler · Accepted solution]

@TenguMan (3) Net Work Duration (Working Hours) - Microsoft Fabric Community

[TenguMan]

This worked perfectly, thank you!