Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
James_Galis1
Helper II
Helper II

Calculate hours

Hello All

 

Would appreciate some assistance with the following.

 

I have 1 table with employee schedules that shows the time and date of visits

I wish to have a calculated column that looks at the start time and end time for each day and calculate the available hours for each employee 


EG Employee 1, for 28/6/23 starts at 9:30 and end time is 15:00, "Available hours" = 5.5

 

The column " available hours " has been entered manually to show the expected result. 

 

ClientVisit DateDayStart TimeEnd TimeEmployee  NameHours Available Hours
Client 127/06/2023Tuesday12:0013:00Employee 11 1
Client 428/06/2023Wednesday9:3010:00Employee 10.5 5.5
Client 528/06/2023Wednesday10:0011:00Employee 11 5.5
Client 228/06/2023Wednesday11:0012:00Employee 11 5.5
Client 328/06/2023Wednesday14:0015:00Employee 11 5.5
Client 729/06/2023Thursday10:0011:00Employee 11 6
Client 829/06/2023Thursday13:0014:00Employee 11 6
Client 629/06/2023Thursday15:0016:00Employee 11 6
Client 930/06/2023Friday11:0012:00Employee 11 4.5
Client 1030/06/2023Friday15:1515:30Employee 10.25 4.5
Client 1126/06/2023Monday9:3010:30Employee 21 2.5
Client 1326/06/2023Monday11:0013:00Employee 22 2.5
Client 1427/06/2023Tuesday9:3010:30Employee 21 3.5
Client 1227/06/2023Tuesday11:0013:00Employee 22 3.5
Client 1228/06/2023Wednesday9:0011:00Employee 22 4.15
Client 1128/06/2023Wednesday11:1513:15Employee 22 4.15
Client 1529/06/2023Thursday11:3013:30Employee 22 2
Client 1630/06/2023Friday9:3011:30Employee 22 4.5
Cleint 1130/06/2023Friday12:0014:00Employee 22 4.5

 

Much appreciated

 

2 ACCEPTED SOLUTIONS

Hi @James_Galis1 again:)
If i understans you correctly after yoour response then something like that:
(I have done it in 3 steps to make it easier to follow , you can combine to 1)
1. Calculate for every employee first start time for each day :

First_Start_time_Employee = CALCULATE(MIN('Table'[Start Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
)
Ritaf1983_0-1688101086993.png

2. Do the same with last end time:

Last_End Time_time_Employee = CALCULATE(MAX('Table'[End Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
)
3. Calculate difference between them in minuts and divide by 60 :
Hours_Availble = DATEDIFF('Table'[First_Start_time_Employee],'Table'[Last_End Time_time_Employee],MINUTE)/60
Ritaf1983_1-1688101201013.png

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

Hi @James_Galis1 
You can summarize your total worked hours with the following:

Hours worked = CALCULATE(sumx('Table',DATEDIFF('Table'[Start Time],'Table'[End Time],MINUTE))/60, ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
)
Ritaf1983_0-1688260878022.png

 

and then calculate the delta:

Hours_Diff = [Hours_Availble]-[Hours worked]

Ritaf1983_1-1688260964998.png

I updated a sample file with those new columns.

link is still Here 
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

9 REPLIES 9
Ritaf1983
Super User
Super User

Hi @James_Galis1 
My apologies, I didn't fully understand the calculation.
Are you interested in aggregation by day and employee?

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi

Thank you for looking into this. I think the "hoursdiff" looks more like the " hours" column 


I need to ensure the unworked hours for each date is captured accordingly

 

Eg

Client 428/06/2023Wednesday9:3010:00Employee 10.5 5.5
Client 528/06/2023Wednesday10:0011:00Employee 11 5.5
Client 228/06/2023Wednesday11:0012:00Employee 11 5.5
Client 328/06/2023Wednesday14:0015:00Employee 11 5.5

 

The total hours worked =3.5

The available hours (9:30 - 15:00) = 5.5

 

The gap of 2 hours which is between client 2 and client 3 is what i need bring into calculation when considering the available hours column

 

Thanks

Hi @James_Galis1 again:)
If i understans you correctly after yoour response then something like that:
(I have done it in 3 steps to make it easier to follow , you can combine to 1)
1. Calculate for every employee first start time for each day :

First_Start_time_Employee = CALCULATE(MIN('Table'[Start Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
)
Ritaf1983_0-1688101086993.png

2. Do the same with last end time:

Last_End Time_time_Employee = CALCULATE(MAX('Table'[End Time]), ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
)
3. Calculate difference between them in minuts and divide by 60 :
Hours_Availble = DATEDIFF('Table'[First_Start_time_Employee],'Table'[Last_End Time_time_Employee],MINUTE)/60
Ritaf1983_1-1688101201013.png

Link to the sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thank You

 

This was what i was looking for

Glad to help, I apologize for the misunderstanding at first 🙂

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi Rita

 

The solution works perfectly.

There is another requirement that has just come up that im currenty working through  but having little trouble with

 

Following on from the solution you have provided.

 

Is it possible to have a measure or column that subtracts the total hours from the available hours ?

 

eg

Client 428/06/2023Wednesday9:3010:00Employee 10.5 5.5
Client 528/06/2023Wednesday10:0011:00Employee 11 5.5
Client 228/06/2023Wednesday11:0012:00Employee 11 5.5
Client 328/06/2023Wednesday14:0015:00Employee 11 5.5

 

For the day of the 28/6

 

Employee 1 has total hours  worked ( 3rd last column )= 3.5 and available hours( last column ) =5.5.

 

The result  im looking to acheive for the 28/6 would 2 ( 5.5 - 3.5 )

 

There is no issue is summing up the hours worked but not sure how to go about just looking at the highest value on the given date and come up with the calculation

 

Cheers

 

 

 

Hi @James_Galis1 
You can summarize your total worked hours with the following:

Hours worked = CALCULATE(sumx('Table',DATEDIFF('Table'[Start Time],'Table'[End Time],MINUTE))/60, ALLEXCEPT('Table','Table'[Employee  Name],'Table'[Visit Date])
)
Ritaf1983_0-1688260878022.png

 

and then calculate the delta:

Hours_Diff = [Hours_Availble]-[Hours worked]

Ritaf1983_1-1688260964998.png

I updated a sample file with those new columns.

link is still Here 
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Thank you so much

This helps me out immensely 

Happy to help 🙂

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.