How to create a running total or balance

[Tina_White]

I’m not able to find an Airtable example of a simple checkbook register. Balance = Previous Balance - Debit + Credit. How would you do that in Airtable?

[Justin_Miller]

The totals line at the very bottom of the spreadsheet does allow you to choose “Sum” for an individual column, but I haven’t figured out a way to do a running total either. It’s a basic formula in Excel that would look something like =SUM(A$2:A2) and then dragged down… but this doesn’t work in Airtable.

And the other problem with the sum total at the very bottom is that it will adjust if you filter records - say if you wanted to view the past 30 days. Anything prior to that filter will not be accounted for in that column total. :frowning:

[Jeremy_Oglesby]

“Views” will be your friend here. A “View” represents a new scope, or query against your data.

You can create a view called “Running Total” that has a filter on it that you never change that looks like this:

This will always show records with dates up to and including today.

To achieve a view where you see records for the last 30 days grouped together including their sum for just the 30 days, but still see the total running balance up to today, create a formula field with a formula like this:

IF(
   IS_BEFORE({Date}, DATEADD(TODAY(), -30, 'days')),
   "1 - Before",
   IF(
      IS_BEFORE({Date}, TODAY()),
      "2 - 30 Day Window",
      "3 - After"
   )
)

Then you can group by this field and you’ll see three groups, all the “before” records, all the “30 day window” records (which is a rolling window based on TODAY()), and all the “after” records. Each group will have its own summary bar, but you’ll still have your total summary bar for all records at the bottom.

You just have to get creative with designing views, or queries against your data.

[Justin_Miller]

That is just way too complicated for an average user that just wants to see a running total next to each entry - like the check book example that was given.

[Noamsay]

Hey,

I got the same question from my client, so I created a video tutorial:

How to show a Running total on Airtable? (6:40 min)

Here is the link to the base used in the tutorial.

Hope it helps!

Noam

[Lexie_Ma31]

Hi Noam,
I am trying to do this but for hundreds of records. How did you quickly populate the 1-1 and 1-to-many to copy in? I’m having a terrible time trying to figure out setting up a cashflow report with a running bank balance.

[Meg_E_Maurer]

THIS IS AMAZING AND INCREDIBLY HELPFUL! Thank you so much.

[stenkate]

A late reply, but maybe useful for other future users:

I haven't implemented it myself, but you can have a look at an automation for this here by community user @TheTimeSavingCo:

https://thetimesaving.company/posts/Accumulative%20Charts?type=projects&referringPage=projects