Forum Discussion
parry2k
Super User
Super UserFinding the most recent value based on selected period
I have a very simple table:
- Calendar Table
- Job Table
Job Date Completed 107 1/1/2017 1.44 107 4/1/2017 10 107 11/1/2017 8 106 4/1/2017 20 106 8/1/2017 15
When a user selected a year/month from slicer, i want the latest completed value
for example, if nov 2017 is selected, i want
107 - 8 (most recent is Nov 1)
106 - 15 (most recent is Aug 1)
if May 2017 selected
107 -10 (most recent is Apr 2017
106 - 20 *most recent is apr 2017
Thanks,
Hi champparry2k
How about this MEASURE
MostRecent = VAR selectedDate = SELECTEDVALUE ( 'Calendar'[Date] ) VAR LatestDate = CALCULATE ( MAX ( JobTable[Date] ), FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] <= selectedDate ) ) RETURN CALCULATE ( SUM ( JobTable[Completed] ), FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] = LatestDate ) )It seems to work with your sample data using Calendar Date as slicer
2 Replies
- Zubair_Muhammad
Community Champion
Hi champparry2k
How about this MEASURE
MostRecent = VAR selectedDate = SELECTEDVALUE ( 'Calendar'[Date] ) VAR LatestDate = CALCULATE ( MAX ( JobTable[Date] ), FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] <= selectedDate ) ) RETURN CALCULATE ( SUM ( JobTable[Completed] ), FILTER ( ALLEXCEPT ( JobTable, JobTable[Job] ), JobTable[Date] = LatestDate ) )- Zubair_Muhammad
It seems to work with your sample data using Calendar Date as slicer