Pivot Table roll-up shows last balance in period.

[alwynK]

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 Qlik Sense  - 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!

[alwynkfinal2]

Anybody?