Forum Discussion

James_Galis1's avatar
James_Galis1
Helper II
3 years ago
Solved

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 ...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    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

  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    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

     

James_Galis1's avatar
James_Galis1
Helper II
3 years ago

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

 

 

 

Ritaf1983's avatar
Ritaf1983
Super User
3 years ago

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