Iterative calculation between columns with the previous row

[r-alrahmani]

Hello, 

Please see the sheet attached showing the available input (includes both Portfolio input file and Text Input for 5-year projection) as well as the expected output table.

Basically, I need to add a column called Closing Portfolio, which is equal to the Opening Portfolio - Repay amount. I need to repeat this for each Fac ID for a 5-year projection. So for the next year, the Opening Portfolio would be equal to the Closing Portfolio of the previous year. 

I tried resolving this issue using the Multi-Row Formula tool. Unfortunately I am not getting the right results

Appreciate any help.

[fmvizcaino · Accepted solution]

Hi @r-alrahmani ,

I'm using a transpose + join to model the dataset prior to multi-row and then doing the calculation as you describe it.

The multi-row function is written as follows:

IF isnull([Row-1:Portfolio])
then [Portfolio]
else [Row-1:Portfolio]-[Row-1:Value]
endif

So if we are in the first line, we will keep the portfolio amount and for all the next rows within the same FacID, we calculate the previous row and replace the value in the current portfolio amount cell.

Best,

Fernando V.