Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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?
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.
But the opening for Feb is hard coded in the script... I am a little confused.
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.
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))