Calculated Column Static Output once Criteria Hit and Doesn't Reverse

[OParker91]

Hi All,

I'm trying to add a column to a Summary Table that shows "Lease-up" until a certain criteria is hit, then it changes to "Stabilised" but once it shows "Stabilised" it doesn't change back to "Lease-up" if it then falls out of the initial criteria.

The criteria is for the change is:
- Released Count = Property Count
- Occupancy >= 90%

Once the criteria is hit, the Status should show as "Stabilised" but if the data then changes and falls out of the initially hit criteria, once it sows as "Stabilised" it doesn't revert back to "Lease-up"

I've tried using a "First hit date" column but this changes week to week when the data updates in to pbi so this doesn't work - maybe I need a seperate date table to reference? but I'm really stuck on this

[bhanu_gautam · Accepted solution]

@OParker91 ,Try using

Status =
VAR CurrentStatus =
IF(
AND(
[Released Count] = [Property Count],
[Occupancy] >= 0.9
),
"Stabilised",
"Lease-up"
)
VAR PreviousStatus =
CALCULATE(
MAX('Summary Table'[Status]),
FILTER(
'Summary Table',
'Summary Table'[Property ID] = EARLIER('Summary Table'[Property ID]) &&
'Summary Table'[Date] < EARLIER('Summary Table'[Date])
)
)
RETURN
IF(
PreviousStatus = "Stabilised",
"Stabilised",
CurrentStatus
)

[OParker91]

Thanks so much! Just as a fallback addition, I added an extra constraint to the final IF to say if last week's status or PreviouStatus was "Stabilised" then use this, otherwise use the current status but you gave me 99% of what I needed. Accepted Solution 🙂