Forum Discussion
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/...
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:
Greg_Deckler
Community Champion
Community ChampionMichaelBauld Well, you use ALL or ALLSELECTED like this:
Measure =
VAR __StartDate = MIN('Calendar'[Date])
VAR __EndDate = MAX('Calendar'[Date])
VAR __MaxDate = MAXX(FILTER(ALL('Table'),[Date] >= __StartDate && [Date]<=__EndDate),[Date])
RETURN
MAXX(FILTER('Table',[Date] = __MaxDate),[KPI])
Greg_Deckler , thanks for the suggestion. I tried using the ALL function as you suggested but I was still getting the same problem of blank values on all months except March (3). Probably doing somethign wrong on my end though!