Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Sample attached for your data
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))