PoverPivot Date

I am having an issue with how to get excel's powerpivot to calculate the most recent date with UPDATING and historical data.I have created a powerpivot to show suppliers audit class scores but when those suppliers are audited multiple times over the years the score changes and I need the powerpivot to display on the most recent date and score. For example, right now I have the count of # of suppliers with risk score 1,2,3,etc. But say supplier ABC is audited in 2011 with a 1 and 2012 with a 2. I need the pivot to count only the most recent score (the 2) so that the count of number of suppliers isn't counting the same supplier twice. This sounds super confusing but I tired uploading a fake example (however powerpivot wasnt working so i used regular pivottable in example but the problem is still the same).

Thank you so much for your answer! Unfortunately that has not solved the problem. I need it to count the max date per each specific supplier name and it is still counting the most recent score of all the suppliers not the most recent of each individual supplier. For example,without your test data we have an audit score of 2 and 3 for supplier ABC but the most recent score is a 3 so in the pivottable it needs to recognize 3 is the most recent score and count only 3, not 2. Therefore, the count of audit score 2 should only be 1.

Thank you so much for your answer! Unfortunately that has not solved the problem. I need it to count the max date per each specific supplier name and it is still counting the most recent score of all the suppliers not the most recent of each individual supplier. For example,without your test data we have an audit score of 2 and 3 for supplier ABC but the most recent score is a 3 so in the pivottable it needs to recognize 3 is the most recent score and count only 3, not 2. Therefore, the count of audit score 2 should only be 1.

mikeTRON, I should probably mention I think you are on the right track but I need it to calculate the max score from the latest DATE of the audit and then return that back to a pivottable. Hope that helps clarify my question. Thanks! :-)