Calculate Hours excluding overlapping timestamp values

[ryan_b_fiting]

Hello Community - 

I had a post on this prior and got a solution, but there is one thing that is throwing this off.  Here is the Original Post 

Below is the current code I am using to calculate the new Hours logged.

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 the one item that is causing issues is if there are time entries that have the exact same starttime.  If the have the same start time, there are not counting overlap hours for some reason.  See the screen shot below, on the first 2 lines in the table, one of those entries should have a 20 min (0.33 hour) overlap.  But they both show 0.00 overlap hours.  

I have tried several things to tweak the formula to account for the exact same starttime across entries, but nothing has worked.

Looking for some quick help as my client has this as an urgent request for payroll processing.

Any help from the community is appreciated!

Thank You

Ryan F

[jgeddes]

Not sure if this will help or not but I noticed the previous records variable was only looking for entries that were less than the current start so it would never find a same start time. 

To solve that I created an index column based on concatenating startTime, endTime and ticketID to prevent duplicates and then adjusted the existing code to look for previous values based on the index number.

Index Calculated Column:

Index =

RANKX(ALL(TimeEntry),CONCATENATE(CONCATENATE(TimeEntry[timeStart],TimeEntry[timeEnd]),TimeEntry[ticket_id]))

Adjusted Code:

Hours Logged Corrected =

VAR CurrentStart = 'TimeEntry'[timeStart]

VAR CurrentEnd = 'TimeEntry'[timeEnd]

VAR CurrentIndex = TimeEntry[Index]

VAR PreviousRecords = FILTER ( TimeEntry, 'TimeEntry'[Index] > CurrentIndex )

VAR PreviousStart = MINX ( PreviousRecords, 'TimeEntry'[timeStart] )

VAR PreviousEnd = MAXX ( PreviousRecords, 'TimeEntry'[timeEnd] )

VAR Starting = MAX ( CurrentStart, PreviousEnd )

VAR Ending = MAX ( CurrentEnd, PreviousEnd )

RETURN

    DATEDIFF ( Starting, Ending, SECOND ) / 3600

Cheers!

[ryan_b_fiting]

Thanks @jgeddes for the suggestion.  I do not think that is going to work.  Tickets are not in any specific order, so members can back date time causing ticket numbers for prior days to be a higher number than tickets for today.  I tried to test it out to see if I was wrong and it would work, but performance on it is very slow and I cannot get the new column to populate.