URGENT!! Classify data of multiple records into buckets using Measure

[tiffanyt123]

Hi! Urgent help required. I have a dataset with columns [RO_Key], [Status], [Valid From Date] and [Valid To Date]. The thing about this dataset is that there might be multiple lines of each [RO_Key] (i.e a few contracts per RO_Key)

My goal is to classify each unique [RO_Key] into either "Pre-committed" / "Committed" / "Future Vacant" based on the selected date in the report ( I created a separate calendar table and a dax measure to filter out where selected date is within valid from and to date)

The logic should be as follows:

1. "Committed" definition --> "Occupied" (for date selected) AND IF there is a next record with a greater [Valid From Date] beyond date selected with [Status] = "Occupied" 

e.g. Date selected = 31 Mar 2022, corresponds to row 5 which has [Status] = "Occupied, and the next record row 6 has [Valid From Date] = 1 May 2022 with [Status] = "Occupied", therefore [Status3] of row 5 should be "Committed".

2. "Pre-committed" Definition: Same as "Committed", except that [Status] = "Vacant" (for date selected) (i.e currently vacant)

3. "Future Vacant": If the next row (assuming date is sorted in ascending order) is MAX([Valid From Date]) of the particular RO_KEY AND next row has [Status] = "Vacant".

My eventual goal is to have just the [RO_KEY] and [Status3] columns, with [Status3] dynamically updating depending on the date I selected.

Any help would be supppppppppper appreciated. Attached is the PBI file. 

https://app.box.com/s/7k9yxdigd8scpb1t67xoa7m1eye4di8g

Perhaps this line of thinking would help: If the selected date falls under one period/record, obtain the rank and status of that line item, find the next line item of the [RO_Key], using rank + 1, and find the status of that line item.. --> but i havent been able to crack it sigh 😞 or would creating a virtual table help??