Forum Discussion
Post PatronCalculating Hours Logged but excluding Overlapping Time
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
2 Replies
- amitchandak
Super User
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])
ryan_b_fitingThanks amitchandak for the quick response. Unfortunately that calculation does not work. For that sample set of data, the result is only 7 hours.
ticket_id Employee company_name timeStart timeEnd Adj_End_Time Hours Logged Adj_Hours_log 82220 55465 Company ABX 7/5/2022 12:00 7/5/2022 20:00 7/5/2022 14:00 8.0 2.0 82220 55465 Company ABX 7/5/2022 14:00 7/5/2022 19:00 7/5/2022 19:00 5.0 5.0
