I am trying to find an expression that will allow me to calculate the Opening balance of an account by period. There are multiple accounts in the table and the layout is like the example below.
For Periods 1-12, I need to pull in the prior period's Period Balance as the current period's Opening balance(i.e., for the Opening balance of February, we will use the Period Balance for January). The Opening balance for Periods 1-12 is different than Period 0, which will use the ending balance of the prior fiscal year.
|Account||Period ID||Opening||Debits||Credits||Net Activity||Period Balance|
You can use as below..
(html, codepage is 1252, embedded labels, table is @1);
Credits-Debits as [Net Activity],
IF(Account = Previous(Account), RangeSum((Credits-Debits),Peek('Balance')),Credits-Debits) as Balance,
IF(Account = Previous(Account), Previous(Credits)-Previous(Debits),0) as Opening
Order By Account, [Period ID];
Drop Table Temp;