Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Post Patron
4 years ago

Calculate Hours excluding overlapping timestamp values

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 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
jgeddes's avatar
jgeddes
Super User
4 years ago

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's avatar
ryan_b_fiting
Post Patron
4 years ago

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.