Calculate Overlapping hours on timestamps

[ryan_b_fiting]

Hello Community - 

I have posted this in the past, but have not gotten any solutions and just wanted to bring this back to the top of the board.  I am banging my head against the wall trying to figure this out......

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

[NamitaS]

Hi ,

Did you get the solution to this ?

I'm facing similar problem. 

[TomMartens]

Hey @ryan_b_fiting ,

please explain why you would expect 8 hours when the red line is not corrected. My expected result for logged hours: nine.
Next question why do you expect 1-hour overlap but zero hours correction as line 3 fits completely into the time range of line 1.

I'm also wondering what happens when two lines have identical start and end times, assuming in the above example, it's line 1 and 3, can the value of  TicketID be used to identify the "first" record ?

Regards,
Tom

[ryan_b_fiting]

Hey @TomMartens the red line is NOT corrected, that is the issue.

1. I would expect that line to have 0 hours corrected and 1 hour of overlap.  But due to the identical start time, those do not show overlap hours.

2. I would expect 1 hour overlap on that line because that line is 1 hour duration and ALL overlap

3.  Yes, ticket id can be used if that were the case, or my index column could be.

Thanks

Ryan

[TomMartens]

Hey @ryan_b_fiting ,

the following DAX is used to create a calculated column:

Tom Hours Log Corrected = 
var currentEmployee = 'Table'[Employee]
var minTimeStart =  CALCULATE( MIN( 'Table'[timeStart] ) , ALLEXCEPT( 'Table' , 'Table'[Employee] ) )
var rankByTicketId = RANKX(
    FILTER( 'Table' , 'Table'[Employee] = currentEmployee )
    , 'Table'[ticket_id] , , asc , Skip
)
var maxHoursLoggedOnMinTimeStart = 
    MAXX(
        FILTER( 'Table'
        , 'Table'[Employee] = currentEmployee && 'Table'[timeStart] = minTimeStart
        )
        , [Hours Logged]
    )
return
SWITCH( TRUE()
    , 'Table'[timeStart] >= minTimeStart && 'Table'[Hours Logged] < maxHoursLoggedOnMinTimeStart && rankByTicketId > 1 , 'Table'[Hours Logged]
    , 'Table'[timeStart] >= minTimeStart && 'Table'[Hours Logged] < maxHoursLoggedOnMinTimeStart && rankByTicketId = 1 , 'Table'[Hours Logged]
)

using the ranked Ticket_ID helps to overcome more than one event start at the same time, Some conditions are missing, but I hope this gives you an idea:

 Hopefully, this provides what you need to tackle your challenge.

Regards,
Tom

[ryan_b_fiting]

Hey @TomMartens I used this in a few different ways now and it still is not giving me the results I expect.  Ticket_ids can be scattered (ie you can backdate tickets on new tickets so the order does not always remain consistent).  Also, any admin work done for logging time does not include a ticket id.  I have also tried with distinct index field too

My inital formula below is very close with the only exception being the exact same start times.  I have tried to tweak with the ranking and index fields as well, but have not had any luck.  Any ideas what I am doing wrong with my measure below?

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

Thanks for all the help with this,

Ryan

[TomMartens · Accepted solution]

Hey @ryan_b_fiting ,

here you will find a little pbix file
CreateIndexColumnUsingPowerQuery.pbix
where I create an index column using PowerQuery, what makes this index column a little special is the fact that the index value reset to 1 for each "group" (here it's the employeeid). I also use a sort by timeStart (please be aware that PowerQuery is case-sensitive with column names). What makes this different in contrast to RANKX is that there are different values even if the startTime is the same.
Now I have an index column that will help to decide on the various if statements.
You have to use the Advanced Editor to see the modification I did after the grouping, there is also a link to a blog article of one where I explain this technique in more detail.

Hopefully, this will finally help to tackle your challenge.
If not, create a pbix that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well. Do not forget to describe the expected result based on your sample data.

Regards,
Tom

[ryan_b_fiting]

I tried this but it didn't give me the results I was looking for, so not sure why someone else accepted as solution. I also have 500k rows of data and growing in my table. So I'm not sure how expensive this solution is on performance if I can get it to work. 

I will publish a PBIX file with some sample data and model for you this evening. Thanks @TomMartens 

[ryan_b_fiting]

PBIX - Overlap Sample 

Overlap Sample - Excel Data 

@TomMartens I have attached just a sample set data in PBIX and then attached the xlsx file.

Only included the TimeEntry table as that is the only table involved in this current problem I am trying to tackle.

Thanks again for all your input and help on the issue!