Change in bank balance

[K21]

Hello

I would like to build a base where I have tables with planned income, planned cost and summary table that reflects incomes and costs on monthly basis.

My question is, if it is possible to take bank balance as a basis for the calculation and calculate Bank balance +income - cost for each record so that the basis for each calculation would be the result of previous row? 

For clarity, here is how I would like it to work:

I have seen some similar questions here as well, but I am not sure if they would work for this use case as well.

Thank you in advance.

[Dimitris_Goudis]

Hey @K21, 

My suggested base structure is: 
- Invoices 
- Bank transactions
- forecasts 
- Months

Separate back transactions from Invoices because it is possible to have more than one bank transaction for each invoice

Having your months in a separate table and adding one month per record that can be linked to each Invoice, Bank transaction, and forecast record helps you aggregate data at the monthly level, and this is what you want to build. 

In the video below, I explain the importance of forecasting, and it shows the result on a graph where you can, of course, add your final earnings field to be displayed. Let me know if that works. Happy to guide you more 🙂 

https://www.linkedin.com/posts/dimitris-goudis_accurate-forecasting-boosts-growth-activity-727518265...

Thanks
Dimitris Goudis

[TheTimeSavingCo]

Hmm I think you can try:
1. Creating a formula field that'll output the total for that month, i.e. 159201 like you've got in your screenshot
2. Create an automation that'll run on the first of day of each month at 12am, and its action would be to look at the previous month's record, grab the 'Total for that month' value, and paste it into the 'Bank balance' field
  - To identify the previous month's record you could use a Date field to set each record's month, and then use a formula field to determine it:

The formula for 'Is last month' is:

IS_SAME(
  DATEADD(
    TODAY(),
    -1,
    'months'
  ),
  Date,
  'month'
)

Link to base