Last balance value in pivot

[RadovanOresky]

Hi,

I have a problem with getting the last value of balance sheet accounts in the pivot table. Does anybody has an expression which could do it?

My case is like this:

- closing balances are calculated in the script for every account and every day

- I need to get a pivot table where dimensions are month, week and accounts group

- I need to get an expression where for any shown period it sums the balances for all selected accounts but only from the last day of that period

I attached an excel whit desired result.

[Jason_Michaelides]

Do you have a calendar table in your QV data model?  If not, create one. There are lots of examples in this forum of how to do this.

One way of getting what you want is to ensure you have IsMonthEnd and IsWeekEnd fields in the calendar model. You can create these in the calendar load by:

If(Date=MonthEnd(Date),1,0)        AS     IsMonthEnd

,If(Date=WeekEnd(Date)-2,1,0)     AS     IsWeekEnd

Then, in your chart with Week as the last dimension your expression can be:

Sum({<IsWeekEnd={1}>} Balance)

And in the chart to month level:

Sum({<IsMonthEnd={1}>} Balance)

Hope this helps,

Jason

[RadovanOresky]

Thanks, this works nicely in the restricted circumstances. Trouble is when adding accounts group dimension in front of the month. When I collapse, it will show the sum of balances, but the last balance from the last month is needed.

I used this expression, which works until date level, but without week level:

If(Sum({<IsMonthEnd={1}>}Balance)=0, Sum(Balance), Sum({<IsMonthEnd={1}>}Balance))

Then, when the pivot is expanded on monthly level it works fine, e.g.:

Account Group	Month	Balance
Group1	1	100
Group1	2	200

But when I collapse the group, the sum is incorrect, e.g.:

Account Group	Balance
Group1	300 - but should be 200

Also, when I want to have month and week in the same pivot and I expand the expression, then when the date for the end of the week is the same as the date for the end of the month, value will duplicate.

So, I guess I hoped there is a more general function, which would always show the last value (timewise) for whichever dimension. I will try to optimize it further.

But really thanks.

[RadovanOresky · Accepted solution]

Not that it matters anymore, but I solved the problem with FirstSortedValue() function.

The expression was FirstSortedValue( Aggr( Sum(Balance), Date ), -Date)

This blog post by HIC expains the approaches to non-additive numbers, like Account Balance.

Additive and Non-Additive Numbers‌

Radovan