Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to Solution.
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 :
2. Do the same with last end time:
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi @James_Galis1
You can summarize your total worked hours with the following:
and then calculate the delta:
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
Hi @James_Galis1
My apologies, I didn't fully understand the calculation.
Are you interested in aggregation by day and employee?
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
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 :
2. Do the same with last end time:
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Thank You
This was what i was looking for
Glad to help, I apologize for the misunderstanding at first 🙂
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
Hi @James_Galis1
You can summarize your total worked hours with the following:
and then calculate the delta:
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
Thank you so much
This helps me out immensely
Happy to help 🙂
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |