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

Time between to dates without weekends and holidays

Hey everbody

 

I'm new to Power Bi and Power Pivot. I've been trying to search this community and found some diffrent solutuins but can't seem to get them to work.

 

I want to calculate the number of days between two timestamps but exclude Saturday, Sunday and holidays in the result. So these are incoming requests to a ServiceDesk and then the response.

Datestamp.jpg

I got a calendar with holydays marked as 0 and working days marked as 1 so it looks like this:

 

Workdays.jpg

 

 

We measure from the time part to the next time part as well as the date if that makes any sense rather than just from one day to00:00 the next day.

 

I'm also thinking we might try to calculate it even more accurately because people allocate items to support in the evening but it doesn't get looked at until we are operational the next working day.  I might be really over complicating this though!

 

Would anyone have any idea if this is possible and if so how to achieve this?

I tried to use this formula: https://community.powerbi.com/t5/Desktop/Calculate-Days-between-two-time-stamps-without-weekends-or/... But it just gave me a result of 1, instead of the difference in days.

I need the measurement from Created day and time, to closed day and time, but without the holydays and time outside the business hour in ServiceDesk. The Business hour in Servicedesk is Monday to Friday from 07:00 to 17:00. (07:00AM to 05:00 PM)

 

Best regards.

1 ACCEPTED SOLUTION

Accepted Solutions
Kasper_Clausen Frequent Visitor
Frequent Visitor

Re: Time between to dates without weekends and holidays

Hi @v-huizhn-msft Angelia

I need the calculation not to include holydas and time outside buisness hours.

Here is the two data sheets as a table:

ClosedDate

 

ClosedTimeCreatedDateCreatedTime
    
30. april 201722:30:0225. april 201708:49:03
    
30. april 201722:30:0227. april 201712:29:18
30. april 201722:30:0226. april 201710:57:02
    
30. april 201722:30:0226. april 201708:54:25
    
    
30. april 201722:30:0227. april 201708:42:22
30. april 201722:30:0227. april 201707:28:22
30. april 201722:30:0126. april 201715:08:43
29. april 201722:30:0126. april 201711:22:29
29. april 201722:30:0226. april 201709:48:14
29. april 201722:30:0226. april 201709:06:10
29. april 201722:30:0226. april 201707:50:06
29. april 201722:30:0226. april 201707:45:22

 

DATODATEKEYMonth keyMonthKvartalWorkdayHelligdagsNavnDayName
        
1. april 2015201504014April21 onsdag
2. april 2015201504024April20Skærtorsdagtorsdag
3. april 2015201504034April20Langfredagfredag
4. april 2015201504044April20 lørdag
5. april 2015201504054April20Påskedagsøndag
6. april 2015201504064April202. påskedagmandag
7. april 2015201504074April21 tirsdag
8. april 2015201504084April21 onsdag
9. april 2015201504094April21 torsdag
10. april 2015201504104April21 fredag
11. april 2015201504114April20 lørdag
12. april 2015201504124April20 søndag
13. april 2015201504134April21 mandag
14. april 2015201504144April21 tirsdag
15. april 2015201504154April21 onsdag
16. april 2015201504164April21 torsdag
17. april 2015201504174April21 fredag
18. april 2015201504184April20 lørdag
19. april 2015201504194April20 søndag
20. april 2015201504204April21 mandag
21. april 2015201504214April21 tirsdag
22. april 2015201504224April21 onsdag
23. april 2015201504234April21 torsdag
24. april 2015201504244April21 fredag
25. april 2015201504254April20 lørdag
26. april 2015201504264April20 søndag
27. april 2015201504274April21 mandag
28. april 2015201504284April21 tirsdag
29. april 2015201504294April21 onsdag
30. april 2015201504304April21 torsdag
        

 

Best regards

Kasper

2 REPLIES 2
v-huizhn-msft Super Contributor
Super Contributor

Re: Time between to dates without weekends and holidays

Hi @Kasper_Clausen,

Based on my understanding, you want to create number of days from CreatDate07:00 to ClosedDate17:00? Could you please post your sample data in table rather than picture, it's hard to reproduce your scenario if your data in picture. Thanks for understanding.

Best Regards,
Angelia

Kasper_Clausen Frequent Visitor
Frequent Visitor

Re: Time between to dates without weekends and holidays

Hi @v-huizhn-msft Angelia

I need the calculation not to include holydas and time outside buisness hours.

Here is the two data sheets as a table:

ClosedDate

 

ClosedTimeCreatedDateCreatedTime
    
30. april 201722:30:0225. april 201708:49:03
    
30. april 201722:30:0227. april 201712:29:18
30. april 201722:30:0226. april 201710:57:02
    
30. april 201722:30:0226. april 201708:54:25
    
    
30. april 201722:30:0227. april 201708:42:22
30. april 201722:30:0227. april 201707:28:22
30. april 201722:30:0126. april 201715:08:43
29. april 201722:30:0126. april 201711:22:29
29. april 201722:30:0226. april 201709:48:14
29. april 201722:30:0226. april 201709:06:10
29. april 201722:30:0226. april 201707:50:06
29. april 201722:30:0226. april 201707:45:22

 

DATODATEKEYMonth keyMonthKvartalWorkdayHelligdagsNavnDayName
        
1. april 2015201504014April21 onsdag
2. april 2015201504024April20Skærtorsdagtorsdag
3. april 2015201504034April20Langfredagfredag
4. april 2015201504044April20 lørdag
5. april 2015201504054April20Påskedagsøndag
6. april 2015201504064April202. påskedagmandag
7. april 2015201504074April21 tirsdag
8. april 2015201504084April21 onsdag
9. april 2015201504094April21 torsdag
10. april 2015201504104April21 fredag
11. april 2015201504114April20 lørdag
12. april 2015201504124April20 søndag
13. april 2015201504134April21 mandag
14. april 2015201504144April21 tirsdag
15. april 2015201504154April21 onsdag
16. april 2015201504164April21 torsdag
17. april 2015201504174April21 fredag
18. april 2015201504184April20 lørdag
19. april 2015201504194April20 søndag
20. april 2015201504204April21 mandag
21. april 2015201504214April21 tirsdag
22. april 2015201504224April21 onsdag
23. april 2015201504234April21 torsdag
24. april 2015201504244April21 fredag
25. april 2015201504254April20 lørdag
26. april 2015201504264April20 søndag
27. april 2015201504274April21 mandag
28. april 2015201504284April21 tirsdag
29. april 2015201504294April21 onsdag
30. april 2015201504304April21 torsdag
        

 

Best regards

Kasper

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 245 members 2,873 guests
Please welcome our newest community members: