Carrying over previous month $$ into new month

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

Developer Tools

Accepted answers

binu_acs

@kauser One way of doing this

Screenshot 2023-07-19 223722.png

Transpose_190723.yxmd

All comments

binu_acs

@kauser One way of doing this

Screenshot 2023-07-19 223722.png

Transpose_190723.yxmd

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.

Rolling Balance.yxmd

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.

binu_acs

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703