Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Change in bank balance

Topic Labels: Data Formulas Workflow Design
431 4
cancel
Showing results for 
Search instead for 
Did you mean: 
K21
4 - Data Explorer
4 - Data Explorer

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:

K21_0-1734605136192.png

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.

 

 

4 Replies 4
Dimitris_Goudis
10 - Mercury
10 - Mercury

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

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:

Screenshot 2024-12-19 at 10.41.38 PM.png

The formula for 'Is last month' is:

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

Link to base

K21
4 - Data Explorer
4 - Data Explorer

Hello

Thank you for the answer and sorry for the late reply.

As I am quite new to the topic, then could I ask about the formula for 'Is last month' - what does it actually show in this case?

And for the automation - the first action should be Find records based on 'is last month'? And what should be the second action for grabbing the 'Total for that month' value, and pasting it into the 'Bank balance' field?

And maybe you happen to have better understanding if this kind of structure is relevant for financial planning which is actually my ultimate goal?

Kind regards.

As I am quite new to the topic, then could I ask about the formula for 'Is last month' - what does it actually show in this case?

It checks whether the value in the 'Date' field is in the previous month, so in January 25 it'd output '1' for any records where the 'Date' value is in Dec 24

---
And for the automation - the first action should be Find records based on 'is last month'?

Yeap that's right

---
And what should be the second action for grabbing the 'Total for that month' value, and pasting it into the 'Bank balance' field?

An 'Update record' action that uses the results from the previous 'Find record' step, specifically the 'Total for that month' value, and then pastes it into the triggering recrod's 'Bank Balance' field

---
And maybe you happen to have better understanding if this kind of structure is relevant for financial planning which is actually my ultimate goal?

Yeah I think this makes sense to do!  This'll let you keep historical accounts of how much the balance was for each month