View My Drafts
I have a problem I'm trying to solve with a DAX measure but have not been able to come up with a formula that will work. I have parent events that have a varied number of child events. I'm trying to accumulate duration of these child events grouped by parent but need to exclude overlapping time.
Example of data for a single parent would look like this. The Non-Overlapping column does not exist. I'm looking for how I can calculate the total of non-overlapping time for a parent. It does not matter which of the children the overlap is cut out of.
Graphically the children for this parent would look like this:
I can get the duration of each individual event by End – Start. However how would I go about getting the Non-Overlapping time that should total 55 time units? I'm looking for the duration of the red line above.
Times = GENERATESERIES(0, MAX('Table'[End])+5,5)
2 - The duration measure
VALUES ( Times[Time Value] ),
"Count of ranges",
VAR _Time = Times[Time Value]
COUNTROWS ( 'Table' ),
_Time < 'Table'[End]
&& _Time >= 'Table'[Start]
) >= 1,
5 -- Because each time range is 5 units
[Count of ranges]
) + 0
Here is the result I get