Forum Discussion

MichaelBauld's avatar
MichaelBauld
Frequent Visitor
4 years ago
Solved

Find the last recorded value based on filter context

Hi there,   This is probably a very simple problem, but for some reason I am really struggling. I have a table with 3 rows which represent annual KPI measures:   Date                  KPI 01/03/...
Need Help
  • MarkLaf's avatar
    MarkLaf
    4 years ago

    The basic approach I think you want is to 1) get latest date for time period in your visual, 2) get all KPIs from latest date from #1 and earlier, 3) get the most recent KPI in subset from #2.

    Here is a measure that follows this approach:

     

    Latest KPI = 
VAR _LastDtInRange = MAX( 'Calendar'[Date] )
VAR _AllCurrentAndPreviousKPIs = CALCULATETABLE( KPIs, 'Calendar'[Date] <= _LastDtInRange )
VAR _LatestKPIrow = TOPN( 1, _AllCurrentAndPreviousKPIs , KPIs[Date] , DESC )
VAR _LatestKPIval = LASTNONBLANK( CALCULATETABLE( VALUES( KPIs[KPI] ), _LatestKPIrow ), 1 )
RETURN
_LatestKPIval

     

    Output (note it works in year, month, quarter, etc. filter context):

    FYI here is the model I set up for this to work. Note that I'm using same sample data you provided in initial post:

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

Please ensure that you have a Calendar Table with calculated columns for Year, Month name and Month number.  Sort the Month name by the Month number.  There should be a relationship (Many to One and Single) between the Data Table and the Calendar Table.  To your slicers/filters, drag Year and Month name from the Calendar Table and select any one Year and Month name.  Write this measure

= CALCULATE([KPI],LASTNONBLANK('Calendar'[Date],CALCULATE([KPI])))

Hope this helps.