Add Business Hours to a Date/Time Value

[Halo-Fan]

Hi All,

I have a custom date table along with a custom holiday table where I use a measure to calculate the number of business hours between two dates.  This allows me to calculate if a request has met the SLA which is based on the number of business hours from the opening of the request.

What I have not been able to do is create a measure where I can have a start date and add a given number of business hours and calculate the End Date which takes into account the actual working hours(exclude hoildays, off hours, weekends). 

For example: lets say working hours are M-F 8AM-5PM (and not Holidays) and these requests must be resolved within 2 Business Hours.  A request comes in at 7 AM on Sat Aug 6, 2022.  The End date SLA should be 10 AM Mon Aug 8, 2022 and that would be the target to beat.

Does anyone have any thoughts on approach for this ?

Thank you in advance.

[v-yalanwu-msft]

Hi, @Halo-Fan ;

Can you post sample file without sensitive information and result what you want to output like below.

https://community.powerbi.com/t5/Desktop/Help-on-Cumulative-total-and-Shifting-PQ-to-CQ/td-p/2529493

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

[Halo-Fan]

@v-yalanwu-msft  - thank you for your response.  

Unfortunately I am not able to share my PBIX file due to confidentiality.  

Here is the situation.  I have a Custom Date table with working hours linked to a holiday schedule - with the two I can use a measure to calculate the number of business hours between two dates.   I also have a fact table full of support tickets and related data. 

For example if business hours are 8 AM - 5 PM and a ticket comes in Friday @ 4:59 PM and is resolved Monday @ 8:01 AM the business hours calculation would be 2 minutes.

What I am looking to do is a variation on the previous scenario.  I want to add time in business hours to a given start date - In this case say 2 business hours I want to add to a request's open time.  If the open time is out of business hours then the counter would not start until 8 AM the next business day.  Simply put it would be like a function returning a date/time value where the input would be a start date/time and a duration (say 2 business hours), and the output would be a date/time 2 business hours later based on the custom calendar and holiday schedule.

Let me know if that helps and again thank you in advance.

[Halo-Fan]

@amitchandak  - thank you very much for your response.  I have attemped to integrate the concept into my model but have additional complexities such as working hours each day, custom holiday calendar and the need to add 1, 2 and other variable number of business hours to determine the date.

I will continue to look at ways to incorporate the content from your blog but at this point I have not been able to.  I will update if able to.  Thank you

[amitchandak]

@Halo-Fan , I have a blog to add business days.  The code for column is in comments

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

see if that can help to build rest of the logic