Forum Discussion

TenguMan's avatar
TenguMan
Frequent Visitor
2 years ago
Solved

How to limit duration to a given time range

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 NumberCase OpenedCase ClosedDuration (Hours)
CS00000018:00:00 AM8:00:00 PM12
CS00000028:00:00 AM7:00:00 AM23
CS00000037:00:00 PM7:59:00 AM12.98

 

After this calculation, the table should look like this:

Case NumberCase OpenedCase ClosedDuration (Hours)
CS00000018:00:00 AM8:00:00 PM12
CS00000028:00:00 AM7:00:00 AM12
CS00000037:00:00 PM7:59:00 AM

1

 

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

Need Help

3 Replies

  • halfglassdarkly's avatar
    halfglassdarkly
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    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.