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)
|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 ^
Hi @chippy635 ,
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
Hi @chippy635 ,
Could you please post your desired result if possible?