Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Solved! Go to Solution.
Hi champ@parry2k
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
Hi champ@parry2k
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |