Calculate Hours logged but exclude any overlapping time stamps

[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
82220	55465	Company ABX	7/5/2022 12:00	7/5/2022 20:00	8.0
82220	55465	Company ABX	7/5/2022 14:00	7/5/2022 19:00	5.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.

Is there a DAX measure that could do this so that I will not include any of the overlapping time stamps in my Hours Logged Calculation?  Note that these are not only perfect round hour overlaps, but also minutes and seconds.

Any help would be greatly appreciated as this is a time sensitive matter.

Thanks Community!

Ryan

[tamerj1]

Hi @ryan_b_fiting 
Please refer to sample file with the proposed solution https://www.dropbox.com/t/NBrrtiTnvplw3Ess

This solution is based on hour granularity. If this is the case also with the real data then it should work. A date column is created first for better accuracy and performance. I know there must be some cases where it won't work and I already have ideas how to overcome these difficulties but first I need you to please test it on your real data and return back to me with all the issues so I don't do unnecessary confusing code. I can also adjust the code to accommodate more than two records per employee per date. Little more details about the structure of your data would also be greatly helpful to tweak and optimize the code.

Hours Logged Corrected = 
VAR CurrentStart = TimeSheet[timeStart]
VAR CurrentEnd = TimeSheet[timeEnd]
VAR CurrentEmpolyeeTable = 
    CALCULATETABLE ( 
        TimeSheet, 
        ALLEXCEPT ( TimeSheet, TimeSheet[company_name], TimeSheet[Employee], TimeSheet[Date] ) 
    )
VAR PreviousRecords = FILTER ( CurrentEmpolyeeTable, TimeSheet[timeStart] < CurrentStart )
VAR PreviousStart = MAXX ( PreviousRecords, TimeSheet[timeStart] )
VAR PreviousRecord = FILTER ( PreviousRecords, TimeSheet[timeStart] = PreviousStart )
VAR PreviousEnd = MAXX ( PreviousRecord, TimeSheet[timeEnd] )
VAR PreviousStart1 = COALESCE ( PreviousStart, CurrentStart )
VAR PreviousEnd1 = COALESCE ( PreviousEnd, CurrentEnd )
VAR CurrentPeriod = GENERATESERIES ( HOUR ( CurrentStart ), HOUR ( CurrentEnd ), 1 )
VAR PreviousPeriod = GENERATESERIES ( HOUR ( PreviousStart1 ), HOUR ( PreviousEnd1 ), 1 )
RETURN
 IF ( 
    ISEMPTY ( PreviousRecords ),
    DATEDIFF ( CurrentStart, CurrentEnd, HOUR ),
    COUNTROWS ( EXCEPT ( CurrentPeriod, PreviousPeriod ) )
 )

[ryan_b_fiting]

Thanks @tamerj1  for the response.  Unfortunately this does not work.  I get an error:

I also need this in seconds (and then divided by 3600 so I do not only have full rounded hours.

[tamerj1]

@ryan_b_fiting 

One more question cause I'm wondering if I over complicated the issue, do you want to completely delete the repeated record or you want to deduct the over laping period out of it? as this is what my code does. 

[tamerj1]

Hi @ryan_b_fiting 
This is a calculated column not a measure. Please create a new calculated column. It can be adjusted to a measure if needed but I guess there is no necessity for that. Also please clarify further the "Seconds" and provide more details. Thank you

[ryan_b_fiting]

I want to just exclude the overlapping time.  I need the total time to initially be in seconds, and then adjust it to hours by dividing by 3600 to get partial hours.  Not all time entries are exact hours.

Also, I have adjusted this to a calculated column, but the performance on it takes a big hit.  My current table is about 200k entries and growing.

[tamerj1 · Accepted solution]

Hi @ryan_b_fiting 
This should be much faster https://www.dropbox.com/t/TdSh89Eo4HIuvQ1R

Hours Logged Corrected Seconds 1 = 
VAR CurrentStart = TimeSheet[timeStart]
VAR CurrentEnd = TimeSheet[timeEnd]
VAR CurrentEmpolyeeTable = 
    CALCULATETABLE ( 
        TimeSheet, 
        ALLEXCEPT ( TimeSheet, TimeSheet[company_name], TimeSheet[Employee], TimeSheet[Date] ) 
    )
VAR PreviousRecords = FILTER ( CurrentEmpolyeeTable, TimeSheet[timeStart] < CurrentStart )
VAR PreviousStart = MINX ( PreviousRecords, TimeSheet[timeStart] )
VAR PreviousEnd = MAXX ( PreviousRecords, TimeSheet[timeEnd] )
VAR Starting = MAX ( CurrentStart, PreviousEnd )
VAR Ending = MAX ( CurrentEnd, PreviousEnd )
RETURN
    DATEDIFF ( Starting, Ending, SECOND ) / 3600

[ryan_b_fiting]

Hey @tamerj1 I just wanted to reach out on this again as I am still having the small issue with the overlapping time if the timestart is the exact same time stamp.  I have tried multiple different things, but cannot find a solution.  Any idea on how to adjust the current calculated column above to account for the timestamps that are identical in the timestart column?

Thanks in advance for any advice!

Ryan

[ryan_b_fiting]

Hey @tamerj1 this is working great, I am just running into one snag.  When the timestart has the exact same time stamp, the corrected hours are not showing it as having overlap.  See the image below (the first 2 lines....line 2 should have 0.33 hours as overlap and Corrected Hours should =0).

I have tried to tinker with the logic, but cannot get the solution I am looking for.  Any help would be tremendously appreciated!

Thanks

Ryan

[tamerj1]

Hi @ryan_b_fiting 
Somehow I missed your reply. Apologies for not responding earlier.

I can see that you are not including the Company Name in the ALLEXCEPT function as proposed by my code. Any logic behind that?

[ryan_b_fiting]

Hey @tamerj1 no problem. Thanks for the response now. 

I excluded it only because it did not change the output whether it was included or not when I first tested it. 

Just trying to wrap my head around why the overlap will not work on exact same start times. I've tried using an index, time start ranking column, >= operator but no luck. 

[tamerj1]

@ryan_b_fiting 

Do you still have the same problem when you include company name in ALLEXCEPT

[ryan_b_fiting]

Hey @tamerj1 yes I do.  I get the same results whether the Company Name is in there or not.  Everything is working with the exception of the lines with the exact same timestart.

[tamerj1]

@ryan_b_fiting 

Ok. What was the number of the sample file I shared with you? Preferably if you update the data to include the case of the same start time. Let me work on it

[ryan_b_fiting]

I'm not sure what the number was. But I do believe the sample has 2 items with same start time. I will try to upload a PBIX file with dummy data if needed. Thanks @tamerj1 

[tamerj1]

@ryan_b_fiting 

Yes please do

[ryan_b_fiting]

Overlapping Time Sample 

There is the link to the PBIX file.  I only have a subset of the data in there from one table (only table being used for this issue).  Let me know if you need the xlsx used for the sample data.

[tamerj1]

Hi @ryan_b_fiting 
It more than just having same starting datetime. I was able to solve the duplicate start datetime easily. I Also solved the problem where both Start and End DateTimes are exactly the same for two records. However, I found out that your data is much more complex than this. In some cases where the time extends over two days (see below example). The current solution is comparing only the records that have the same start date but this example proofs that this is not going to work.

Finding a solution for such complex cases requires a lot of time and concentration which unfortunately I don't have right now. I will work on it perhaps at weekend.

[ryan_b_fiting]

@tamerj1 got it.  Thanks for the explanation.  Any way you can provide the solution you used to resolve the same timestart and timeend issue?  I would like to see how that was resolved as well.

Thanks for all the time helping me out.

[tamerj1]

@ryan_b_fiting 

Hours Logged Corrected Seconds 1 =
VAR CurrentStart = timeentry[timeStart]
VAR CurrentEnd = timeentry[timeEnd]
VAR CurrentID = timeentry[id]
VAR CurrentEmpolyeeTable =
    CALCULATETABLE (
        timeentry,
        ALLEXCEPT (
            timeentry,
            timeentry[company_id],
            timeentry[member_id],
            timeentry[Start Date]
        )
    )
VAR PreviousRecords =
    FILTER ( CurrentEmpolyeeTable, timeentry[timeStart] <= CurrentStart )
VAR CurrentStatEndTable =
    FILTER (
        PreviousRecords,
        timeentry[timeStart] = CurrentStart
            && timeentry[timeEnd] = CurrentEnd
    )
VAR BreakTieStart =
    FILTER ( PreviousRecords, timeentry[timeEnd] < CurrentEnd )
VAR BreakTieStartEnd =
    FILTER (
        PreviousRecords,
        timeentry[timeEnd] <= CurrentEnd
            && timeentry[id] < CurrentID
    )
VAR PreviousEnd1 =
    MAXX ( BreakTieStart, timeentry[timeEnd] )
VAR PreviousEnd2 =
    MAXX ( BreakTieStartEnd, timeentry[timeEnd] )
VAR PreviousEnd =
    IF ( COUNTROWS ( CurrentStatEndTable ) = 1, PreviousEnd1, PreviousEnd2 )
VAR Starting =
    MAX ( CurrentStart, PreviousEnd )
VAR Ending =
    MAX ( CurrentEnd, PreviousEnd )
VAR Result =
    DATEDIFF ( Starting, Ending, SECOND ) / 3600
RETURN
    Result

[ryan_b_fiting]

@tamerj1 thanks for the DAX.  Unfortunately that does not calcualte anything as overlap time, even timestarts that are not the exact same:

Hours Logged Corrected Seconds 1 = 
VAR CurrentStart = '2 - CW - TimeEntry'[timeStart]
VAR CurrentEnd = '2 - CW - TimeEntry'[timeEnd]
VAR CurrentID = '2 - CW - TimeEntry'[id]
VAR CurrentEmpolyeeTable =
    CALCULATETABLE (
        '2 - CW - TimeEntry',
        ALLEXCEPT (
            '2 - CW - TimeEntry',
            '2 - CW - TimeEntry'[company_id],
            '2 - CW - TimeEntry'[member_id],
            '2 - CW - TimeEntry'[Start Date]
        )
    )
VAR PreviousRecords =
    FILTER ( CurrentEmpolyeeTable, '2 - CW - TimeEntry'[timeStart] <= CurrentStart )
VAR CurrentStatEndTable =
    FILTER (
        PreviousRecords,
        '2 - CW - TimeEntry'[timeStart] = CurrentStart
            && '2 - CW - TimeEntry'[timeEnd] = CurrentEnd
    )
VAR BreakTieStart =
    FILTER ( PreviousRecords, '2 - CW - TimeEntry'[timeEnd] < CurrentEnd )
VAR BreakTieStartEnd =
    FILTER (
        PreviousRecords,
        '2 - CW - TimeEntry'[timeEnd] <= CurrentEnd
            && '2 - CW - TimeEntry'[id] < CurrentID
    )
VAR PreviousEnd1 =
    MAXX ( BreakTieStart, '2 - CW - TimeEntry'[timeEnd] )
VAR PreviousEnd2 =
    MAXX ( BreakTieStartEnd, '2 - CW - TimeEntry'[timeEnd] )
VAR PreviousEnd =
    IF ( COUNTROWS ( CurrentStatEndTable ) = 1, PreviousEnd1, PreviousEnd2 )
VAR Starting =
    MAX ( CurrentStart, PreviousEnd )
VAR Ending =
    MAX ( CurrentEnd, PreviousEnd )
VAR Result =
    DATEDIFF ( Starting, Ending, SECOND ) / 3600
RETURN
    Result