Status Change

Hi dbattin4 ,

Thanks danextian , srlabhe  and OktayPamuk80  for the detailed suggestions.

The approaches were on point, but the missing piece was that my dataset doesn’t always have entries on the exact month-end date. That’s why my original measure returned blanks.
As for my knowledge we  can solve it by tweaking the logic to grab the latest record on or before the previous month end instead of looking for that single date.
Here’s the final measure that worked for me (sharing in case it helps others too).

PreviousMonthState = 
VAR _currentOpp = SELECTEDVALUE(Opportunities_ME[OpportunityNumber]) 
VAR _currentDate = MAX(Opportunities_ME[IngestionDate_EOM]) 
VAR _prevMonthEnd = EOMONTH(_currentDate, -1)
VAR _lastAvailableDate = CALCULATE( MAX(Opportunities_ME[IngestionDate_EOM]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) )
VAR _state = CALCULATE( MAX(Opportunities_ME[StateCode]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ) )
RETURN _state

Really appreciate all the guidance here.

Thanks,
Akhil.