FirstSortedValue in Pivot Table with many dimensions

alwynkfinal2 — Fri, 08 Dec 2023 09:14:14 GMT

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?

Re: FirstSortedValue in Pivot Table with many dimensions

Chanty4u — Sun, 10 Dec 2023 04:47:14 GMT

try this

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

topic Re: FirstSortedValue in Pivot Table with many dimensions in App Development

FirstSortedValue in Pivot Table with many dimensions

Re: FirstSortedValue in Pivot Table with many dimensions