Pivot Table roll-up shows last balance in period.

alwynK — Sat, 16 Nov 2024 17:56:25 GMT

Good day,

I am currently building a balance sheet in a pivot table that has dimension groups with sub accounts on the left, and as columns, I have the year, quarter and months. When I expand the months, like in the below screenshot, it works perfectly and the sheet balances every month.

However, when I collapse the field to just show the quarter, I want to see the last month's balance as the quarter data, not the 3 months summed, because balance sheets are "closing balances" and not movement so I don't want them added together:

This is currently what it looks like:

I would very much for it to look like this:

The data in my fact table looks like this - idPeriod connects to my calendar to give me years/quarters/months:

My current expression looks like this : FirstSortedValue(Cumulative,-idPeriod)

It basically looks at the last sorted value based on idPeriod and brings back that amount. When I put this in a straight table in - I get the answers I want - but when I put it in a pivot table, it all just breaks and doesn't work at all

Please help!

Re: Pivot Table roll-up shows last balance in period.

alwynkfinal2 — Mon, 22 Apr 2024 14:23:55 GMT

Anybody?

topic Re: Pivot Table roll-up shows last balance in period. in App Development

Pivot Table roll-up shows last balance in period.

Re: Pivot Table roll-up shows last balance in period.