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.
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??