Core Hours Duration Filter Using NetWorkDays

[Anonymous]

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

[v-danhe-msft]

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

[Anonymous]

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

[v-danhe-msft]

Hi @Anonymous ,

Could you please post your desired result if possible?

Regards,

Daniel He