Calculating Overlap Hours in Timestamps /Timelogs

[ryan_b_fiting]

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 ) / 3600

But 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

[v-zhangti]

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

@v-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?