Calculate last weeks balance

I am trying to recreate historical balance by day. I have two tables : 1)Current balance table that provides today's balance and 2) a History table that contains historical transaction information.

Can someone help create a descending running balance by day for each account? I have attached mock data .

Thank you very much,

Balance and Transactions.xlsx

Transformation

Preparation

Accepted answers

ponraj

You can try attached workflow.

Calculate last weeks balance_solution.yxmd

All comments

ivoller

Hi,

I'm not sure exactly what you need to achieve but this may help.

balance.yxmd

NicoleJohnson

Here's another option that I think gets you the result your looking for - joins the sources by account, sorts by descending date, and then uses a Multi-Row formula to calculate the balance from prior day.

Let us know if either of these options don't line up with what you are trying to achieve! And if so, an example of your desired output would be helpful to narrow down the solution. Thanks!

Cheers,

Daily Balance.yxmd

Albert

Hi Nicole - It is very close to the desired output.. Is there any way I can stack the current balance above the previous day balance? lets pretend current balance is 4/19/2018

Account	Date	Trans Amount	Balance
A	4/19/2018		11081.15
A	4/18/2018	884	10197.15
A	4/17/2018	-37.59	10234.74
A	4/16/2018	-26.95	10261.69
A	4/15/2018	-8.08	10269.77

ponraj

You can try attached workflow.

Calculate last weeks balance_solution.yxmd

Albert

Thank you!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703