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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

 Client Visit Date Day Start Time End Time Employee  Name Hours Available Hours Client 1 27/06/2023 Tuesday 12:00 13:00 Employee 1 1 1 Client 4 28/06/2023 Wednesday 9:30 10:00 Employee 1 0.5 5.5 Client 5 28/06/2023 Wednesday 10:00 11:00 Employee 1 1 5.5 Client 2 28/06/2023 Wednesday 11:00 12:00 Employee 1 1 5.5 Client 3 28/06/2023 Wednesday 14:00 15:00 Employee 1 1 5.5 Client 7 29/06/2023 Thursday 10:00 11:00 Employee 1 1 6 Client 8 29/06/2023 Thursday 13:00 14:00 Employee 1 1 6 Client 6 29/06/2023 Thursday 15:00 16:00 Employee 1 1 6 Client 9 30/06/2023 Friday 11:00 12:00 Employee 1 1 4.5 Client 10 30/06/2023 Friday 15:15 15:30 Employee 1 0.25 4.5 Client 11 26/06/2023 Monday 9:30 10:30 Employee 2 1 2.5 Client 13 26/06/2023 Monday 11:00 13:00 Employee 2 2 2.5 Client 14 27/06/2023 Tuesday 9:30 10:30 Employee 2 1 3.5 Client 12 27/06/2023 Tuesday 11:00 13:00 Employee 2 2 3.5 Client 12 28/06/2023 Wednesday 9:00 11:00 Employee 2 2 4.15 Client 11 28/06/2023 Wednesday 11:15 13:15 Employee 2 2 4.15 Client 15 29/06/2023 Thursday 11:30 13:30 Employee 2 2 2 Client 16 30/06/2023 Friday 9:30 11:30 Employee 2 2 4.5 Cleint 11 30/06/2023 Friday 12:00 14:00 Employee 2 2 4.5

Much appreciated

2 ACCEPTED SOLUTIONS
Super User

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])
)

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

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
Super User

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])
)

and then calculate the delta:

Hours_Diff = [Hours_Availble]-[Hours worked]

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
9 REPLIES 9
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
Helper II

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 4 28/06/2023 Wednesday 9:30 10:00 Employee 1 0.5 5.5 Client 5 28/06/2023 Wednesday 10:00 11:00 Employee 1 1 5.5 Client 2 28/06/2023 Wednesday 11:00 12:00 Employee 1 1 5.5 Client 3 28/06/2023 Wednesday 14:00 15:00 Employee 1 1 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

Super User

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])
)

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

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
Helper II

Thank You

This was what i was looking for

Super User

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
Helper II

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 4 28/06/2023 Wednesday 9:30 10:00 Employee 1 0.5 5.5 Client 5 28/06/2023 Wednesday 10:00 11:00 Employee 1 1 5.5 Client 2 28/06/2023 Wednesday 11:00 12:00 Employee 1 1 5.5 Client 3 28/06/2023 Wednesday 14:00 15:00 Employee 1 1 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

Super User

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])
)

and then calculate the delta:

Hours_Diff = [Hours_Availble]-[Hours worked]

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
Helper II

Thank you so much

This helps me out immensely

Super User

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors