topic Re: Calculate Hours logged but exclude any overlapping time stamps in DAX Commands and Tips

Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Wed, 20 Jul 2022 03:01:01 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

amitchandak — Wed, 20 Jul 2022 03:37:40 GMT

ryan_b_fiting ,

Create a new column and use that

var _next = minx(filter(Table, [Employee] = earlier([Employee]) && [ticket_id] = earlier([ticket_id]) && [timeStart] > earlier([timeStart]) ),[timeStart])
return
if(_next < [timeEnd], _next , [timeEnd])

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Wed, 20 Jul 2022 13:03:12 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Wed, 20 Jul 2022 13:28:36 GMT

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.

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Wed, 20 Jul 2022 13:38:13 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Wed, 20 Jul 2022 13:46:02 GMT

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.

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Wed, 20 Jul 2022 14:00:14 GMT

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.

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Wed, 20 Jul 2022 14:22:37 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Wed, 20 Jul 2022 14:42:55 GMT

Thank you tamerj1 this is faster, a little slow still, but it may be due to being on my clients RD connection.

It also appears that the calc is working as expected after testing a handful of entries! Thank you so much for your consistent communication!

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Tue, 16 Aug 2022 03:13:50 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Mon, 30 Jan 2023 21:26:36 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Tue, 31 Jan 2023 05:22:46 GMT

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?

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Tue, 31 Jan 2023 05:41:23 GMT

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.

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Tue, 31 Jan 2023 06:55:52 GMT

ryan_b_fiting

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

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Tue, 31 Jan 2023 16:46:15 GMT

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.

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Tue, 31 Jan 2023 16:59:27 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Wed, 01 Feb 2023 23:10:57 GMT

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

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Thu, 02 Feb 2023 02:41:34 GMT

ryan_b_fiting

Yes please do

Re: Calculate Hours logged but exclude any overlapping time stamps

ryan_b_fiting — Thu, 02 Feb 2023 03:24:14 GMT

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.

Re: Calculate Hours logged but exclude any overlapping time stamps

tamerj1 — Thu, 02 Feb 2023 08:16:57 GMT

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.