Carrying over previous month $$ into new month

[kauser]

Hello, 

How can i carry over previous month ending balances into next month beginning balances. please note january beginning $$ already populated in file. i need from february month. please see below my sample how can i transfer jan ending $110 into feb beginning, feb ending $150 into Mar beginning and Mar ending $200 into Apr beginning 

	JAN	FEB	MAR	APR
Beginning $$	50	110	150	200
Running $$	60	40	50	50
Total	110	150	200

Thank You 

[binuacs · Accepted solution]

@kauser One way of doing this

[ScottLewis]

This gets easier to solve if you pivot the data to have your time in rows. Doing so lets you use multi-row formula to do the moving calculation.

Making the assumption that you have as input the January Beginning Balance and all of the Running values, the attached workflow creates your rollforward by calculating the beginning value for each month using a multi-row formula and then a regular formula to fill in ending. 

[kauser]

Thanks for your quick response. can please let me know how to convert null to zero because on my I have multiple rows and lines with null so when I apply your formula its consider all nulls. but I want to apply this formula only for beginning balances. 

[ScottLewis]

That is a job for the Data Cleansing tool. Drop it in where you need to conversion done and have a look at the configuration. One of the selectable options is to do exactly that. I think it may even be one of the defaults for numeric fields. If it doesn't just work, check to make sure your data is stored as numeric and if not put in a select before the data cleansing to re-type to some appropriate number format.

[binuacs]

@kauser You can also use the Imputation tool to make the NULL to 0