FirstSortedValue in Pivot Table with many dimensions

[alwynkfinal2]

Hey Everyone,

I'm having trouble with my firstsortedvalue formula. I'm creating a balance sheet for a company, and as such I want to enhance the formula to show the balances as at the last month of a particular year, because if you currently select the year only (while using a sum) it sums up the balances for the entire year. I currently force the user to pick a financial year-month combo to see the balances:

The idea is to tell the system to show the balance for the latest month when you only select a year:

When I show it per year, it rolls up by sum, so I want to use firstsorted value to say:

Give me the last balance of the last Fin YM for that Year and display it instead of summing the entire year:

I tried something along these lines. I want it to aggregate the sum by Fin YM and Account Level 1, but then sort only by Fin YM.

=FIRSTSORTEDVALUE(
    AGGR(SUM([Cumulative]), [Fin YM], [Account Level 1]), 
    -[Fin YM]
)

 
Any ideas on how to achieve this?

[Chanty4u]

try this

FirstSortedValue(
AGGR(SUM({<Year={'$(=Only(Year))'}>} [Cumulative]), [Fin YM], [Account Level 1]),
-[Fin YM]
)