Measure to select most recent value of a dimension, based on the date filtered

[alexei7]

I'm struggling to come up with the DAX to product a measure which will give me the "most recent" status for a product.
The report contains a date slicer, so the "most recent date" for the status would be dependent on the date range the user had filtered on. 

My source data is one large table containing the following fields:

ProductID
ProductName
Location
Date
Status
ProductCategory
ProductAbbreviation
Many other dimensions which should be ignored for this report...

My desired output is a table with the following:

ProductID
ProductName
Location
Most recent status - new measure

Notes

- Data is added/updated monthly
- Products can be at more than one location
- Users will be able to filter on the date

The pbix model with some fake data in it is here:

https://mega.nz/file/ao1n3STS#uDqZD0TH-cG5-8_YlWu5PZSS0k6PVaaikXUtb4KSJxg

[amitchandak · Accepted solution]

@alexei7 , Add this

lastnonblankvalue(Table[Date], max(Table[Status]))

or

calculate(max(Table[Status]), filter(Table, Table[Date] = calculate(max(Table[Date]), allexcept(Table, Table[Product ID]))))

or

calculate(max(Table[Status]), filter(Table, Table[Date] = calculate(max(Table[Date]), allexcept(Table, Table[Product ID], Table[Location]))))

[alexei7]

Thanks for your help - worked perfectly.