Remove Non-Business Hours from time duration

[Ashton_Quincey]

I'm still new to Power BI and DAX calculations so bare with me if this is a rookie question.

We receive cases at any time during any day of the week. Our team works 24x5 so we should be able to cover all week days. However there are some regions/countries that have specific business hours and our team is being penalized for when we receive a case during non-business hours.

Below are the business hours base on our teams coverage.

EMEA: Monday at (00:30 UTC) to Saturday at (00:00 UTC) 

APJ: Monday at (00:30 UTC) to Saturday at (00:00 UTC)  

United States: Monday - Friday (13:00 UTC - 22:00 UTC)

Canada: Monday at (00:30 UTC) to Saturday at (00:00 UTC) 

Latin America: Monday - Friday (11:00 UTC - 21:00 UTC) 

I have created a custom column that shows the time duration of [timestamp_case_completed]-[timestamp_case_created]. If any of those cases are created during non-business hours, I would like for those non-business hours to be removed from the duration as our team is not able to work those cases.

In my head, I'd imagine it be something like: (please excuse the elementary calculation)

IF[region] = EMEA then remove (Saturday 00:00 UTC to Monday 00:30 UTC)  from [timestamp_case_completed]-[timestamp_case_created].

IF[country] = United States then remove (22:00 UTC - 13:00 UTC M-F and Saturday,Sunday) from [timestamp_case_completed]-[timestamp_case_created].

IF[region] = LATAM then remove (21:00 UTC - 11:00 UTC M-F and Saturday,Sunday)  from [timestamp_case_completed]-[timestamp_case_created].

I am not sure how to go about this considering we have weekend and non-business hours on weekdays.

Any help would be greatly appreciated on this. I've watched videos on YouTube but none go in depth on this level for me.

[v-yiruan-msft]

Hi @Ashton_Quincey ,

You can refer the following links to get it:

Exclude weekends and holidays:

DAX:

networking days excluding holiday and weekend

WorkingDaysCount = 
// Assuming weekend is Sat & Sun .... adjust if required
CALCULATE (
    COUNTROWS ( 'WorkDate' ),
    FILTER (
        'WorkDate',
        NOT ( 'WorkDate'[WeekDay] IN { "Sat", "Sun" } )
            && RELATED ( Holidays[Date] ) = BLANK ()
    )
)

Power Query:

Working hours without Holidays, weekends and by country in power query

Exclude non-working hours:

Power BI calculating time difference excluding weekends and non working hours

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards