I am trying to write a lookup DAX function in powerpivot to return the audit score on the latest audit date. (see below):
Supplier Audit Date Score Latest Audit Date
ABC 1/1/2012 2 5/24/2016
ABC 4/4/2014 3 5/24/2016
DEF 1/2/2015 1 1/2/2015
GHI 5/5/2016 2 5/5/2016
JKL 5/6/2015 1 5/6/2015
MNO 2/3/2016 1 2/3/2016
PQR 4/3/2016 1 4/3/2016
STU 7/11/2013 1 2/3/2016
VWX 8/21/2012 1 8/21/2012
YZ 9/10/2011 3 9/10/2011
STU 2/3/2016 5 2/3/2016
ABC 5/24/2016 1 5/24/2016
I cannot figure out this formula. For example, since the latest date for the audit on ABC supplier is 5/24/2016 I need it to tell me that the audit score for supplier ABC is 1. This will eventually be turned into a count of how many suppliers are in each risk class based on their LATEST score so I need it to only have 1 line per supplier, not showing 1 next to everytime ABC appears. Any help please? Thank you!