Powerpivot-Changing Dates and Audit Scores

[songgirl101]

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 can't upload the document due to this forum rules. The formula I tried so far is: =CALCULATE(max([Audit Date]),filter(DATA,DATA[Supplier Name]=EARLIER(DATA[Supplier Name]))) which calculates the latest audit date but I can't figure out how to choose the audit score on that date. The end result I need to get is 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.

 

[akice]

Making a few assumptions here. Notably that you have a separate Calendar dimension table related to DATA table and the pivot has Supplier ID on the rows. One possible measure to use is:

Last Audit Class Score := CALCULATE( SUM(DATA[Audit Class Score]), LASTNONBLANK( Calendar[Date],  SUM( DATA[Audit Class Score] ) ) )

Also assumes that there is only one Audit Class Score per Supplier per Audit Date. Even though I use SUM if there is only one Audit Class Score on that date that value will be returned.

 

[songgirl101]

Making a few assumptions here. Notably that you have a separate Calendar dimension table related to DATA table and the pivot has Supplier ID on the rows. One possible measure to use is:

Last Audit Class Score := CALCULATE( SUM(DATA[Audit Class Score]), LASTNONBLANK( Calendar[Date],  SUM( DATA[Audit Class Score] ) ) )

Also assumes that there is only one Audit Class Score per Supplier per Audit Date. Even though I use SUM if there is only one Audit Class Score on that date that value will be returned.

Thank you! It gives me an error that there it cannot find table calendar. Is there a way to fix that? My data is as follows (the max audit date is irrelevant as I don't want max I was just testing it out) :

Supplier Name	Audit Date	Audit Score	Max Audit Score	LatestAuditDate
ABC	1/1/2012	2	3	4/4/2014
ABC	4/4/2014	3	3	4/4/2014
DEF	1/2/2015	1	1	1/2/2015
GHI	5/5/2016	2	2	5/5/2016
JKL	5/6/2015	1	1	5/6/2015
MNO	2/3/2016	1	1	2/3/2016
PQR	4/3/2016	1	1	4/3/2016
STU	7/11/2013	1	5	2/3/2016
VWX	8/21/2012	1	1	8/21/2012
YZ	9/10/2011	3	3	9/10/2011
STU	2/3/2016	5	5	2/3/2016
TEST	7/11/2013	1	5	2/3/2016
TEST	8/21/2012	3	5	2/3/2016
TEST	9/10/2011	5	5	2/3/2016
TEST	2/3/2016	4	5	2/3/2016
TEST	7/11/2013	2	5	2/3/2016

<tbody>
</tbody>

The formula I have for latest audit date is: =CALCULATE(max([Audit Date]),filter(DATA,DATA[Supplier Name]=EARLIER(DATA[Supplier Name]))) I thought I could use that to match what the score is on that date but no luck and not sure thats what needs to happen.

 

[akice]

[COLOR=#333333]Last Audit Class Score := CALCULATE( SUM(DATA[Audit Class Score]), LASTNONBLANK( DATA['Audit Date'],  SUM( DATA[Audit Class Score] ) ) )[/COLOR]

With this measure you don't need the last two columns of your test data.

 

[songgirl101]

[COLOR=#333333]Last Audit Class Score := CALCULATE( SUM(DATA[Audit Class Score]), LASTNONBLANK( DATA['Audit Date'],  SUM( DATA[Audit Class Score] ) ) )[/COLOR]

With this measure you don't need the last two columns of your test data.

Unfortunately that only returns the audit score that is already there, not the latest audit score. I have pasted below the data and what I need the end result to look like. The end result is the audit score and count of audit score columns. The count of audit scores counts only the most recent score. I need to find some way to automate this so excel/powerpivot calculates it instead of a manual calculation. Thanks for your help!

[TABLE="width: 593"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Supplier Name[/TD]
[TD]Audit Date[/TD]
[TD] Audit Score[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Audit Score[/TD]
[TD] Count of Audit Score[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD="align: right"]4/4/2014[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD="align: right"]1/2/2015[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD="align: right"]5/5/2016[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]JKL[/TD]
[TD="align: right"]5/6/2015[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MNO[/TD]
[TD="align: right"]2/3/2016[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PQR[/TD]
[TD="align: right"]4/3/2016[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STU[/TD]
[TD="align: right"]7/11/2013[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VWX[/TD]
[TD="align: right"]8/21/2012[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YZ[/TD]
[TD="align: right"]9/10/2011[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]STU[/TD]
[TD="align: right"]2/3/2016[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEST[/TD]
[TD="align: right"]7/11/2014[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEST[/TD]
[TD="align: right"]8/21/2012[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEST[/TD]
[TD="align: right"]9/10/2011[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEST[/TD]
[TD="align: right"]2/3/2016[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEST[/TD]
[TD="align: right"]7/11/2013[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[akice]

Not sure I understand what you want the final pivot to look like. Per my post above I assumed just Supplier and the last audit score in pivot. Are you wanting Audit Score and Count of audit score? If so how will you know for which Supplier the score is associated? Or do you really just need to know how many suppliers under each latest Audit Score?