Time measures excluding weekends

[Lexi]

Hello Power BI experts!

I am looking for help on some time calculations. I am fairly new to Power bi so would need step to step guide (teaching a baby to walk👶) Date table below:

Scenario 1: 

I need to calculate the business hours between two time stamps but to only calculate the business hours (8AM-4PM) and to exclude weekends. 

Scenario 2:

I need to calculate how long its been in the queue, so from created date to now (todays date and time) and to exlude weekend time and to be within the businees working hour. 

From here I need to hightlight if we are gone above our criteria of priority 1 = 5 actual business days and priority 2 = 2 days.

Id like to include the total diffence in hours in one column and and the no days in another column. I am sorry if there has been an answer already but nothing seems to work for me and I am totally confused 😞

Thanks,

Lexi 🦄

Customer ID	Created Date	Completed Date	Stage	Priority
00000001	21/01/2020 15:37		New	Priority 1
00001642	09/05/2020 10:59		New	Priority 2
00236325	27/04/2020 07:53	27/04/2020 09:35	Completed	Priority 1
01000344	20/05/2020 04:27	21/05/2020 07:25	Completed	Priority 2
01013450	18/06/2020 04:27		New	Priority 1
01002678	18/06/2020 05:27	19/06/2020 04:27	Completed	Priority 1

[harshnathani]

Hi @Lexi ,

Check these solutions for time difference

https://poszytek.eu/en/microsoft-en/office-365-en/powerbi-en/powerbi-datediff-only-working-days-hour...

https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374255/highlight/true

https://community.powerbi.com/t5/Desktop/Time-difference-between-two-date-times-excluding-weekends-a...

https://community.powerbi.com/t5/Desktop/Calculating-Working-Hours-Between-Two-Dates-Times/td-p/7301...

https://www.youtube.com/watch?v=bs3yzmf9elA

https://www.youtube.com/watch?v=GLIoDbOiJgw

https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[lbendlin]

You are not specifying what your weekend days are. You are also not specifying if the data comes from different time zones. You have not specified how long a business day is.  (Assumption would be Sat/Sun, no, and eight?)

Have you already created a Dates table (sometimes also called Calendar table)?  Usually that is where you add the "workday"  flag. Is that table only used in one timezone or is it based on UTC ? Fun fact - the Power BI service thinks in UTC. Endless entertainment for scenarios like yours.

[Lexi]

Hi Ibendlin,

Thanks for replying. 

Weekends would include Saturday and Sunday. No different time zones only GMT. Business hours would be in fact 8 am till 4pm Mon-Fri. 

I do have a calendar I created using the formula Calenderauto(). Hope this is sufficient.

Thanks!

Lexi.

[lbendlin]

One more thing . you have not specified how to handle exceptions, ie when one or both of your timestamps happen to be on weekends or on workdays but outside the agreed business hours? Do you penalize users for that?

[Lexi]

Hi again,

I have tried all links with no success.

The dax code, seems to be the closest thing but an error comes up: the start or end date in calendar function can not be blank. Even if I put the created date and completed date.

Working Hours =

VAR filtered =

FILTER (

ADDCOLUMNS (

CROSSJOIN (

CALENDAR ( [Created Date],[Completed Date] ),

With the exceptions, I was thought that the "timer" would count from the next date or time ie Mon-FRi 8am-5pm. 

Thanks,

Lexi

[harshnathani]

Hi @Lexi ,

Can you please share a sample pbix file.

Regards,

Harsh Nathani