Dynamic most recent value to filter visuals

[datadiver]

Hi, 

I'm having trouble finding a way to get PowerBI show the most recent value within a category for the purposes of visualisation. I need the way of finding most recent value to be dynamic, as the report will have a time slicer so we can see the 'most recent' value on given dates going backwards. My data looks something like this:

Date	Category	Rating
07/10/2019	A	Red
02/10/2019	B	Green
01/10/2019	C	Yellow
22/09/2019	B	Yellow
22/09/2019	A	Yellow
15/09/2019	C	Green

And I might have a bar chart that counts the number of Red/Green/Yellowon a specific date (determined by a date slicer on the page), based on the most recent value within the A, B, C categories.

So if the date is set at 23rd September 2019, the bar chart would show 2 Yellow, 1 green and 0 reds. 

Hope this makes sense and any help would be much appreciated

[v-diye-msft · Accepted solution]

Hi @datadiver ，

Please let me know if you'd like to get the results like this:

I added another calendar table as slicer, then add the measures to filter the recent rating which within A/B/C category, note that measure4 is not blank. please find the pbix here: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EfCWbzJAx0tPgjbKNQDWpA...

[datadiver]

Hi @v-diye-msft ,

Thank you for taking the time to look at this and provide a response. 

Unfortunately, Measure 4, which I think you have used to filter the table, has a syntax error when I have downloaded the .pbix you provided, so it doesn't seem to solve my problem. 

Also, does this aoproach work with visuals (e.g. a bar chart) rather than just tables? 

[v-diye-msft]

Hi @datadiver ,

Sorry I uploaded the incomplete one before, please try below link:

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EQddvrOVZ49Hn3UsTWEA2e...

[v-diye-msft]

Hi @datadiver ,

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,
Dina Ye