Set Analysis Max Report Year where report year is not null

[jeckstein]

I have a table like this.

ID	Year	Amount
AAA	2012	300
AAA	2013	400
AAA	2014	500
BBB	2012	50
BBB	2013	60
CCC	2012	1

I am looking for set analysis that will sum Amount where year is max. I am currently using this calculation in a KPI object.

Sum({$<[Year]={'$(=Max([Year]))'}>}[Amount])

The result is 500. This is because the Max(Year) for the entire group being summed is 2014 and there is no data for 2014 for ID "BBB" and "CCC"

I need the result of 561

AAA    2014     500

BBB    2013     60

CCC   2012     1

What is the set analysis i need.

[sunny_talwar]

May be use this:

FirstSortedValue(Amount, -Year)

or this in a text box object

Sum(Aggr(FirstSortedValue(Amount, -Year), ID))

UPDATE: Added a missed parenthesis at the end

[sunny_talwar]

Sample attached for your data

[sunny_talwar]

If for some reason you have multiple entries for each ID each year, you can try this:

=Sum(Aggr(FirstSortedValue(Aggr(Sum(Amount), ID, Year), -Aggr(Year, ID, Year)), ID))