Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to Solution.
@TenguMan (3) Net Work Duration (Working Hours) - Microsoft Fabric Community
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.
@TenguMan (3) Net Work Duration (Working Hours) - Microsoft Fabric Community
This worked perfectly, thank you!
User | Count |
---|---|
80 | |
74 | |
62 | |
61 | |
46 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |