cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
chippy635 Frequent Visitor
Frequent Visitor

Core Hours Duration Filter Using NetWorkDays

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)
CreatedResourceConfirmedNetWorkDaysNet HrsMinutes
1/31/2019 16:131/31/2019 16:1010 Days 23 Hours 56 Minutes
2/1/2019 10:532/1/2019 10:5010 Days 23 Hours 56 Minutes
2/1/2019 10:552/1/2019 10:5510 Days 23 Hours 59 Minutes
2/1/2019 11:032/1/2019 11:0510 Days 0 Hours 1 Minutes
2/1/2019 11:152/1/2019 11:1510 Days 23 Hours 59 Minutes
2/1/2019 11:202/1/2019 11:2010 Days 23 Hours 59 Minutes
2/1/2019 11:232/1/2019 11:2010 Days 23 Hours 56 Minutes
2/1/2019 11:322/1/2019 11:3010 Days 23 Hours 57 Minutes
2/6/2019 10:062/11/2019 16:1543 Days 6 Hours 8 Minutes
2/7/2019 9:332/11/2019 15:5532 Days 6 Hours 21 Minutes
2/8/2019 12:442/11/2019 16:4021 Days 3 Hours 55 Minutes

 

The sample data is as above ^ 

 

Thanks!

Emma

3 REPLIES 3
Community Support Team
Community Support Team

Re: Core Hours Duration Filter Using NetWorkDays

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chippy635 Frequent Visitor
Frequent Visitor

Re: Core Hours Duration Filter Using NetWorkDays

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

Community Support Team
Community Support Team

Re: Core Hours Duration Filter Using NetWorkDays

Hi @chippy635 ,

Could you please post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.