Forum Discussion

Cbutler's avatar
Cbutler
Helper III
5 years ago
Solved

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 endi...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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's avatar
    Cbutler
    Helper III
    5 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      Cbutler So can you post your sample data as text?