Complex Time Overlap Calculation Help

[bdavis398]

I am trying to calculcate the duration of time for ranked items per patient without having overlap. I have a fictitious data example below. I have figured out how to remove overlap for the same named item per patient, but cannot seem to get the ranking down. Any help is appreciated! (Picture and actual table follow)

Patient	(Resource)	Start Time	End Time	Rank	Hours	Comments
1	Baker Act	1/1/23 12:00 PM	1/3/23 5:00 PM	1	53	Rank 1, so all time is counted.
1	Sitter	1/1/23 5:00 AM	1/3/23 5:00 PM	3	7	Next highest rank, so only time not overlapping with Rank 1 for this patient is counted.
2	Baker Act	1/1/23 5:00 PM	1/4/23 10:00 PM	1	77	Next highest Rank (2), so only time not overlapping with Rank 1 for this patient is counted.
2	Marchman Act	1/1/23 12:00 PM	1/1/23 5:00 PM	2	5	Rank 1, so all time is counted.
2	Sitter	1/1/23 12:00 PM	1/4/23 10:00 PM	3	0	Next highest Rank (3), so only time not overlapping with Ranks 1 & 2 for this patient is counted, which is zero.
3	Sitter	1/1/23 5:00 AM	1/4/23 3:00 PM	3	82	Only one Rank for this HAR , so all time is counted.

[AmiraBedh]

To solve this problem, you'll need to build a logic that follows these steps for each patient:

1. Order the resources by rank.
2. For each resource, check its overlap with higher-ranked resources.
3. Count the hours for the resource that are not overlapping with higher-ranked resources.

Here's a possible DAX solution using a series of calculated columns and then summing up the hours:

1. Calculate the `Start Time` and `End Time` for the highest rank for each patient:

Start Time Rank 1 =
CALCULATE(
MIN('Table'[Start Time]),
FILTER('Table', 'Table'[Rank] = 1 && 'Table'[Patient] = EARLIER('Table'[Patient]))
)

End Time Rank 1 =
CALCULATE(
MAX('Table'[End Time]),
FILTER('Table', 'Table'[Rank] = 1 && 'Table'[Patient] = EARLIER('Table'[Patient]))
)

2. Calculate the non-overlapping start and end times for resources of rank 2 and 3:

Adjusted Start Time =
SWITCH(
TRUE(),
'Table'[Rank] = 1, 'Table'[Start Time],
'Table'[Rank] = 2, MAX('Table'[Start Time], 'Table'[Start Time Rank 1]),
'Table'[Rank] = 3, MAX('Table'[Start Time], 'Table'[End Time Rank 1]),
'Table'[Start Time]
)

Adjusted End Time =
SWITCH(
TRUE(),
'Table'[Rank] = 1, 'Table'[End Time],
'Table'[Rank] = 2, MIN('Table'[End Time], 'Table'[End Time Rank 1]),
'Table'[Rank] = 3, MIN('Table'[End Time], 'Table'[End Time Rank 1]),
'Table'[End Time]
)

3. Calculate the hours for each rank without overlap:

Adjusted Hours =
IF(
'Table'[Adjusted Start Time] > 'Table'[Adjusted End Time],
0,
DATEDIFF('Table'[Adjusted Start Time], 'Table'[Adjusted End Time], HOUR)
)

Finally, to get the total hours for each patient, you'd simply sum the `Adjusted Hours` column. You can use a table visualization to display the results for each patient, and also show the resource, rank, and comments.

[bdavis398]

Hi! Thank you so much for the prompt response, it is almost there! I took an example of real data, blurring PHI. HAR = Patient in this case, and the other items are irrelevant for this purpose. I added the columns as described and received the below output:

Sorting by rank, the marchman act was active from 1:32AM up until the time the baker act started, 4:34 PM, or roughly 9 hours, in addition to the roughly 31 baker act hours. Other patients (last two rows are a patient each) only had sitter order, but netted 0 hours. Did I do something wrong?