Previous value not working??

[Cbutler]

Hi there, 

I have a simple table which outlines a measure name, the RAG of that measure against a date (quarter ending date).

The table doesnt have dates for every day, just on the quarter ending date. 
I have made a Calendar table to link up to this date. 

I have converted the "RAG" in to a value Red = 1, Amber= 2, Green = 3 and anything else = 1000. 
I am trying to bring back the "Previous value" so that I can create a Trend calculation, however everything I have tried isnt working.
 

Any ideas? 

[Greg_Deckler]

@Cbutler  Can you post that data as text so that I can recreate it in a PBIX file? I don't want to type all that.

[Cbutler]

Thanks, I've extracted some of the data, can you access this link?

link removed 

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = LOOKUPVALUE('RAG by measure ID'[RAG],'RAG by measure ID'[Measure ID],'RAG by measure ID'[Measure ID],'RAG by measure ID'[Date],CALCULATE(MAX('RAG by measure ID'[Date]),FILTER('RAG by measure ID','RAG by measure ID'[Measure ID]=EARLIER('RAG by measure ID'[Measure ID])&&'RAG by measure ID'[Date]<EARLIER('RAG by measure ID'[Date]))))

Hope this helps.

[Cbutler]

Yes @Ashish_Mathur thank you! 🙂 That's got it. 

Out of interest, do you know @Ashish_Mathur @Greg_Deckler why PreviousQuarter didnt work for this calc? 
I have done similar measures in other reports and it has worked great, but this one wasnt having any of it and I don'y understand why....? 

Thanks again for your help both. 

[Greg_Deckler]

@Cbutler - Should be something along the lines of:

Previous rag =
  VAR __Date = [Date]
  VAR __Measure = [Measure]
  VAR __PreviousDate = MAXX(FILTER('Table',[Date]<__Date && [Measure]=__Measure),[Date])
RETURN
  MAXX(FILTER('Table',[Date]=__PreviousDate && [Measure]=__Measure),[RAG])

[Cbutler]

@Greg_Deckler 

I tried this but didnt work 😞

*Previous rag =

VAR __Date = ALL('QR Measures'[Date])

VAR __Measure = ALL('QR Measures'[Id])

VAR __PreviousDate =

MAXX ( FILTER ( 'QR Measures', 'QR Measures'[Date] < __Date && 'QR Measures'[Id] = __Measure ), 'QR Measures'[Date] )

RETURN

MAXX (

FILTER ( 'QR Measures', 'QR Measures'[Date] = __PreviousDate && 'QR Measures'[Id]= __Measure ),

'QR Measures'[RAG]

)

[Greg_Deckler]

@Cbutler So can you post your sample data as text?