Forum Discussion
Post PatronCalculating Overlap Hours in Timestamps /Timelogs
Hi Community -
I am in need of some help on how to calculate total hours logged, but excluding time stamps that are overlapping by employee. Here is a sample of the data:
| ticket_id | Employee | company_name | timeStart | timeEnd | Hours Logged | Hours Logged Corrected | Overlap Hours |
| 82220 | 55465 | Company ABX | 7/5/2022 12:00 | 7/5/2022 20:00 | 8.0 | 8.0 | 0.0 |
| 82221 | 55465 | Company ABX | 7/5/2022 14:00 | 7/5/2022 19:00 | 5.0 | 0.0 | 5.0 |
| 85648 | 55465 | Company FFF | 7/5/2022 12:00 | 7/5/2022 13:00 | 1.0 | 1.0 | 0.0 |
When I just SUM the hours here I get 13 hours, but the actual result I would want to see is 8 hours, because the entire 5 time log of the second row is overlapping the first log.
Line item in red I would expect 1 hour overlap and 0.0 Hours Logged Corrected
I am 99% of the way there with the below calculated column screenshot (referencing real data model not the sample above):
Hours Logged Corrected =
VAR CurrentStart = '2 - CW - TimeEntry'[timeStart]
VAR CurrentEnd = '2 - CW - TimeEntry'[timeEnd]
VAR CurrentEmpolyeeTable =
CALCULATETABLE (
'2 - CW - TimeEntry',
ALLEXCEPT ( '2 - CW - TimeEntry', '2 - CW - TimeEntry'[member_name], '2 - CW - TimeEntry'[Start Date] )
)
VAR PreviousRecords = FILTER ( CurrentEmpolyeeTable, '2 - CW - TimeEntry'[timeStart] < CurrentStart )
VAR PreviousStart = MINX ( PreviousRecords, '2 - CW - TimeEntry'[timeStart] )
VAR PreviousEnd = MAXX ( PreviousRecords, '2 - CW - TimeEntry'[timeEnd] )
VAR Starting = MAX ( CurrentStart, PreviousEnd )
VAR Ending = MAX ( CurrentEnd, PreviousEnd )
RETURN
DATEDIFF ( Starting, Ending, SECOND ) / 3600But as you see in the screen shot below, the first 2 lines have an exact same starttime which is causing an issue. The second line should hace 0.33 overlap hours for the 20 minutes that overlap, but it is showing 0 because it has the same starttime. I have tried using startime<=CurrentStart in the Previos_Records variable but that shifted ALL hours to overlap hours.
Any help would be greatly appreciated. I have tried so many things on this and have had no luck. I have not had any luck getting answers from the community either.
Thanks Community!
Ryan
2 Replies
v-zhangtiCommunity Support
Hi, ryan_b_fiting
You can try the following methods.
Column:Hours Logged Corrected = Var mintime=CALCULATE(MIN('2 - CW - TimeEntry'[timeStart]),ALLEXCEPT('2 - CW - TimeEntry','2 - CW - TimeEntry'[Employee])) Var maxtime=CALCULATE(MAX('2 - CW - TimeEntry'[timeEnd]),ALLEXCEPT('2 - CW - TimeEntry','2 - CW - TimeEntry'[Employee])) Return SWITCH(TRUE(), [timeStart]<=mintime&&[timeEnd]>=maxtime,[Hours Logge], [timeStart]>=mintime&&[timeEnd]<=maxtime,0, [timeStart]<=mintime&&[timeEnd]<=maxtime,[Hours Logge])Overlap Hours = [Hours Logge]-[Hours Logged Corrected]Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ryan_b_fitingv-zhangti thanks for the message. The result you are showing is what I would expect. I created that column, and the Hours Logged Corrected is showing me 0 on every time entry, so for some reason it is not working for my data. Any ideas why?