Returning Most Recent Value by Category Based on Slicer

[BrightBart]

Need help. I have a table, "Table" that has only 3 columns: Category, Date, Value:

I am looking for the ability to have the user, using a slicer, select any date and then creating a measure that would return the most recent value for each category that is before the user-selected value.

In the example above, if the user selects 7/1/2020, the resultant table in the report would be:

This is pulling the "2" value for the 4/13/2020 row for Category 1 and the "5" value off the 4/3/2019 row for Category 2 and totaling will total them correctly to 7.

I can easily create a measure that gives me the overall latest amount but I'm really stumped on putting it together with the dynamic user selection.

Any help would be greatly appreciated.

[ebeery · Accepted solution]

@BrightBart the model linked below does what you describe, but it is probably just one of many possible solutions.

https://drive.google.com/file/d/1WFwxZ19prv9IzmuAKYLsDyuTsKODCsOO/view?usp=sharing 

[Ashish_Mathur · Accepted solution]

Hi,

You may download the PBI file from here.

Hope this helps.

[BrightBart]

Thank you, this too works perfectly. I will admit that I am not yet good enough to fully understand this but I could easily reapply if needed.

[Ashish_Mathur]

You are welcome.

[ebeery · Accepted solution]

@BrightBart the model linked below does what you describe, but it is probably just one of many possible solutions.

https://drive.google.com/file/d/1WFwxZ19prv9IzmuAKYLsDyuTsKODCsOO/view?usp=sharing 

[BrightBart]

Thank you. Works perfectly.