Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ericsara
Helper I
Helper I

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

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 TimeStatusTicket
2/5/2022 06:48Open1
4/5/2022 14:22Support1
8/5/2022 9:06Admin1
8/5/2022 16:14Support1
24/5/2022 9:55Development1
29/5/2022 12:01Support1
31/5/2022 07:45Development1
7/6/2022 15:01Support1
10/6/2022 10:23Closed1
2/5/2022 19:31Open2
5/5/2022 9:00Support2
8/5/2022 14:20Admin2
8/5/2022 17:55Support2
24/5/2022 11:20Development2
29/5/2022 9:03Support2
2/6/2022 16:16Development2
10/6/2022 08:54Closed2

 

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

Cheers, 

1 ACCEPTED SOLUTION
ericsara
Helper I
Helper I

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

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

View solution in original post

2 REPLIES 2
ericsara
Helper I
Helper I

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

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

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.