Forum Discussion
Previous value not working??
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?
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.
7 Replies
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.
Thanks, I've extracted some of the data, can you access this link?
link removedHi,
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 - 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])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] )RETURNMAXX (FILTER ( 'QR Measures', 'QR Measures'[Date] = __PreviousDate && 'QR Measures'[Id]= __Measure ),'QR Measures'[RAG])Cbutler So can you post your sample data as text?
