Forum Discussion
overlap time calculation
I have time entry data in the following format, and I need to detect overlapping entries for each member. Objective: Identify and flag entries as overlpa or No Overlap. Calculate the durat...
ryan_mayu
Super User
Super Userthe sample data can't be opened. What's the expected output based on the screenshot you provided?
This is my expeted output a column to flag overlap entries and a column for overlap duration calculation .
Overlap_Flag2 =
VAR curMember = 'Table'[member_id]
VAR curStart = 'Table'[start]
VAR curRow = 'Table'[Index]
RETURN
IF(
COUNTROWS(
FILTER(
'Table',
'Table'[member_id] = curMember &&
'Table'[Index] < curRow && -- only previous rows
'Table'[start] <= curStart && -- previous row starts before current row
'Table'[end_] > curStart -- overlap exists
)
) > 0,
"OVERLAP",
"NO OVERLAP"
)
Overlap_Duration_Hours2 =
VAR curMember = 'Table'[member_id]
VAR curStart = 'Table'[start]
VAR curEnd = 'Table'[end_]
VAR curRow = 'Table'[Index]
VAR PrevRows =
FILTER(
'Table',
'Table'[member_id] = curMember &&
'Table'[Index] < curRow && -- only previous rows
'Table'[end_] > curStart -- overlap check
)
VAR OverlapIntervals =
ADDCOLUMNS(
PrevRows,
"OverlapStart", MAX(curStart, 'Table'[start]),
"OverlapEnd", MIN(curEnd, 'Table'[end_])
)
VAR OverlapHours =
SUMX(
OverlapIntervals,
DATEDIFF([OverlapStart], [OverlapEnd], MINUTE) / 60
)
RETURN
IF(OverlapHours > 0, OverlapHours, 0)
above are the 2 meaures that i used and i think I am very close to the solution.