Outstanding balance pivot table Set analysis

[#1]

Hello,

I have daily account balances snapshots and using set analysis to calculate outstanding totals.

The formula sum ({<BalanceDate = {'$(=Max(BalanceDate))'} >} Balance) does work for the latest total but doesn't consider month/day dimensions

Please advise

[MK_QSL]

In Set Analysis, Set Expression is evaluated before the aggregation made. So in this case, Max(BalanceDate) is calculated first which will remain same for all lines. You can't have Max(BalanceDate) for each line different using set analysis.

[#3 · Accepted solution]

Following the advice I've modified the formula

     FirstSortedValue ( Aggr( Sum(Balance), BalanceDate) , -BalanceDate)

and it works in test example

but it loosing calculation if extra dimension added, Company for example

[MK_QSL]

Add company in Aggr