Hi,
I have a history table of RAG statuses and I want to report the last status recorded in each period. (or prior if no status recorded in the period). [RAG] is a text field containing "R", "A", or "G".
I am using the following measure:
Last_RAG = Calculate(max(History[RAG]), REMOVEFILTERS(),History[Modified Date] <= SELECTEDVALUE('Calendar'[Period]) , History[Project_Id] = SELECTEDVALUE('Project Status'[ID]))
But of course this gives me the maximum value recorded so selects R rather than A or G if more than one status happens to be recorded in the same period. I want the last status that was reported up to the end of the period.
After reading through posts I found this thread with a reply by @Greg_Deckler that looked like it would work but doesn't seem to do it for me. Here's how I modified the code: Last_RAG =
VAR _LastDate =
CALCULATE(
MAXX(
FILTER('Calendar',[Date]<= SELECTEDVALUE('Calendar'[Period])),
[Date]
)
,REMOVEFILTERS())
VAR _Result = MINX(FILTER(History, [Modified Date] = _LastDate), History[RAG])
RETURN _Result
