Need help with DAX measure with filters for unique keys (Real Estate Data)

[tiffanyt123]

This project is regarding Real Estate Data.

For a particular unique key, I would like to fill up the [Status3] column with 1) "Committed", 2) "Pre-committed" or 3) "Future Vacant", depending on the date selected from a calendar table.

The logic should be as follows:

"Committed": [Status] = "Occupied" (for date selected) AND [Status] = "Occupied" (for the next record with [Valid From Date] beyond date selected)

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

"Pre-committed": Same as "Committed", except that [Status] = "Vacant" (for date selected).

"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

[TonyZhou1980]

Hi Tiffany，

Do you have other ways to upload the sample file for downloading and review?

Thank you,

Tony

[tiffanyt123]

Hi Tony! https://drive.google.com/file/d/1EQP4eeuWCQ4T1frlE_pTJcbrgR3VOfud/view?usp=sharing would this work?