Carrying over prior months data to current month in Set Analysis Pivot Table

[aetingu12]

All,

I have read some examples on cumulative and rolling balances on this, but I was wondering if it was possible to have a pivot table and within the pivot table have a row that displays prior months closing balance.

So in March 19; Opening Balance should be Feb 19's closing balance. I have attached a sample with data.

Help is much appreciated.

[sunny_talwar]

If we do this for March, why don't we get the Jan's closing balance of 7,952.84 as Feb's opening balance? Why do we have a different number for Feb's opening of 7,889.46... which also equals Feb's closing?

[aetingu12]

That is the problem.  That was my trial.  I would like to get the prior months closing as current months opening for all dates, unless it is the first date.

[sunny_talwar]

But the opening for Feb is hard coded in the script... I am a little confused.

[aetingu12]

Ah, yes sorry.  I was just trying to illustrate what I wanted.  I have attached updated test file with no open date.  Basically, I would like to have a new row that displays the closing date from the prior period.

Thanks for your patience.

[sunny_talwar · Accepted solution]

Check out the attached

Expression used

If(Only(TOTAL <Movements> Movements) = 'Opening' and Len(Trim(Only(TOTAL <Dates> Dates))) > 0, Before(Sum(TOTAL <Dates> {<Movements = {'Closing'}>}Balances)), Sum(Balances))

[aetingu12]

Much appreciated!