Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm trying to calculate the number of business days (excluding holidays) between two date columns: how long it takes to complete a change request from receiving date to assigned date. I'm using the NetWorkDays measure found from https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362
Using the measure I am able to calculate the exact business days duration down to the hour and minute, but the hours are not limited to business hours. I need help to figure out how to calculate for business hours (9:30am to 3:30pm) with the current measure I am using.
Also, if the new request arrives after the ending core hour (3:30pm) then it is scheduled to be completed by the end of the following business day (start at 9:30, end by 3:30pm). If the new request arrives before the starting core hour (9:30am) then it is scheduled to be completed by 3:30pm of the same day. Essentially the filter I want to add is that all requests be completed within 8 core hours.
I can understand how to do this in a string of code, but not in Power BI. Do any of you have any idea how to:
a) code for business hours (9:30am to 3:30pm) only within the meaure I am using (refer to link)
b) incorporate the core hour duration filter (pseudo code below)
Core hours: 09:30 to 03:30 Target = (assigned_lapse) Parameters = (received_timedate) and (assigned_timedate) If (assigned_timedate) <> NUL if TIME(received_timedate) >= (09:30) if TIME(received_timedate) <= (15:30) TIME(received_timedate_calc) = TIME(received_timedate) DATE(received_timedate_calc) = DATE(received_timedate) fi fi if TIME(received_timedate) > (15:30) or if TIME(received_timedate) < 09:30 TIME(received_time_calc) = (09:30) fi assigned_lapse = (assigned_timedate) – (received_timedate_calc)
Created | ResourceConfirmed | NetWorkDays | Net HrsMinutes |
1/31/2019 16:13 | 1/31/2019 16:10 | 1 | 0 Days 23 Hours 56 Minutes |
2/1/2019 10:53 | 2/1/2019 10:50 | 1 | 0 Days 23 Hours 56 Minutes |
2/1/2019 10:55 | 2/1/2019 10:55 | 1 | 0 Days 23 Hours 59 Minutes |
2/1/2019 11:03 | 2/1/2019 11:05 | 1 | 0 Days 0 Hours 1 Minutes |
2/1/2019 11:15 | 2/1/2019 11:15 | 1 | 0 Days 23 Hours 59 Minutes |
2/1/2019 11:20 | 2/1/2019 11:20 | 1 | 0 Days 23 Hours 59 Minutes |
2/1/2019 11:23 | 2/1/2019 11:20 | 1 | 0 Days 23 Hours 56 Minutes |
2/1/2019 11:32 | 2/1/2019 11:30 | 1 | 0 Days 23 Hours 57 Minutes |
2/6/2019 10:06 | 2/11/2019 16:15 | 4 | 3 Days 6 Hours 8 Minutes |
2/7/2019 9:33 | 2/11/2019 15:55 | 3 | 2 Days 6 Hours 21 Minutes |
2/8/2019 12:44 | 2/11/2019 16:40 | 2 | 1 Days 3 Hours 55 Minutes |
The sample data is as above ^
Thanks!
Emma
Hi @Anonymous ,
Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Daniel He
Hi Daniel,
I have updated my post with sample data. Have a look and please let me know if you have any solutions.
Thanks,
Emma
Hi @Anonymous ,
Could you please post your desired result if possible?
Regards,
Daniel He
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |