Sum time based on status and excluding out of business hours time

[ericsara]

Hi Community,

This question builds on the one I asked here. Big thanks to @amitchandak and @ddpl for help on that one.

As I implemented this solution, I discovered I needed more than my original request.

So I have the below data, and I want to know how long any given ticket has been on any given status. I initially asked for this in days, but I now realise what I need is how many minutes.   As I measure this time, I only want to measure between 9:00 and 17:00. Any time outside of this should not be included. I then want to convert this to days rounded to two decimal paces.

Date and Time	Status	Ticket
2/5/2022 06:48	Open	1
4/5/2022 14:22	Support	1
8/5/2022 9:06	Admin	1
8/5/2022 16:14	Support	1
24/5/2022 9:55	Development	1
29/5/2022 12:01	Support	1
31/5/2022 07:45	Development	1
7/6/2022 15:01	Support	1
10/6/2022 10:23	Closed	1
2/5/2022 19:31	Open	2
5/5/2022 9:00	Support	2
8/5/2022 14:20	Admin	2
8/5/2022 17:55	Support	2
24/5/2022 11:20	Development	2
29/5/2022 9:03	Support	2
2/6/2022 16:16	Development	2
10/6/2022 08:54	Closed	2

Any help on how to achieve this would be greatly appreciated.

Cheers, 

[ericsara · Accepted solution]

I found this video that does just want I am looking for. 

(53) Calculate business hours with DAX in Power BI - YouTube

[Ashish_Mathur]

Hi,

Try this

1. In the Query Editor, seperate the Date and Time
2. Create a Calendar Table with a Many to One and Single relationship between the Date column of your Data table to the Date column of the Calendar Table
3. Create a Time Table (which should have 1440 rows - one row for each minute of the day).  Create a Many to One and Single relationship between the Date column of your Data table to the Date column of the Calendar Table
4. To your slicer, drag Time from the Time Table and make a selection
5. Write this measure

Count = countrows(Data)

Hope this helps.