Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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??
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |