Forum Discussion
Find the last recorded value based on filter context
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 _LatestKPIvalOutput (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:
Thanks Greg_Deckler , this seems to work when I use a date slider, but when I have a separate year and month drop down it still delivers blank results when the month selected isn't one that has a value within the KPI table:
Scorecard Screenshot
Would I need to make the value of the KPI a cumulative sum based on the filter of year and month so it delivers a value in all months?
Thanks!
Michael
Super UserThe 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: