Forum Discussion
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/2019 0.65
01/03/2020 0.67
01/03/2021 0.68
I'm building a scorecard view with a range of different measures and tables and for all of them I want to pull into the report the latest values based on the date filter context (drawn from my calendar table). For the above KPI this works fine when I am simply using an annual filter, but as soon as I look at quarters or months (which will be necessary) the value returned to the card is blank as the only month with data is March.
What I am struggling with is how to build a DAX measure which will return the last value within the selected date range regardless of whether the specified month/quarter actually has a recorded value. i.e.
March 2020 = 0.67
July 2020 = 0.67
March 2021 = 0.68
...and so on
Can anyone suggest how I might go about this?
Kind regards
Michael
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:
7 Replies
- Greg_Deckler
Community Champion
MichaelBauld Maybe:
Measure = VAR __StartDate = MIN('Calendar'[Date]) VAR __EndDate = MAX('Calendar'[Date]) VAR __MaxDate = MAXX(FILTER('Table',[Date] >= __StartDate && [Date]<=__EndDate),[Date]) RETURN MAXX(FILTER('Table',[Date] = __MaxDate),[KPI])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
- MarkLaf
Super User
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:
- Ashish_Mathur
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.
