Forum Discussion
Time measures excluding weekends
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 |
6 Replies
- lbendlin
Super User
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.
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?
Hi Lexi ,
Check these solutions for time difference
https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374255/highlight/true
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)
