Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ryan_b_fiting
Post Patron
Post Patron

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 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.  

 

Log.PNG

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

2 REPLIES 2
jgeddes
Super User
Super User

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!
 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.