Carry forward a total?

Create a [Months] table, with each record name indicating the month and year for clarity; e.g. May 2021, June 2021, etc.

Link each record in [Trading Days] to the appropriate month record from [Months]. (Hint: to make this easier, make a view in [Trading Days] that groups by this new linked record field. Adding a record to the group automatically builds the link.)

Create a rollup field in [Months] that rolls up all of the daily totals, which will give you a monthly total.

Also in the [Months] table, add a {Previous Month} link field to link to records in the same table. When you create the next months’ record, manually link this to the previous month’s record. Add a lookup field that lets you look up the previous month’s total using this link.

If you want to see this previous month total in [Trading Days], add a lookup field there to pull it in. Because each day’s record is tied to a month, it has access to that month’s details, including the total from the previous month.

Thank you for preventing the meltdown of my brain!

I was SO close.

This: " add a {Previous Month} link field to link to records in the same table." was the thing I didn’t come up with. Everything else was there, but I didn’t explore linking to the same table. I even read some examples of doing that but they were for different purposes and I still didn’t twig it. Duh.

Have a great day! :beers:

@RGCreative Glad to know that you got the answer you were seeking! If you would, please mark my comment (the one above, not this one) as the solution to your question. This helps others who may be searching with similar questions. Thanks!

I’m within a whisker of the full thing, but I realise not quite there because…

The aim “show the previous month’s closing “cash on hand” anywhere - which is the starting point for this month of course…” is now half met. I can show the previous month’s closing - but it isn’t the starting point for the current month yet. The remaining half is that it needs to add into the current month total, so that becomes the new carry forward. Eek, I think this is another roadblock but I’m not working on it until tomorrow.

That should just be a matter of adding a formula field to the [Months] table to add the current month’s total to that from the previous month. If there’s no previous linked month, it should just add zero.

Thanks for coming back on this.

I have the extra column (see below, ), but it isn’t quite right.

Line 3 {Last Month CF} value needs to be Line 2 {Total Cash} plus Line 3 {Cashflow In/Out} so the CF is cumulative. I know, Airtable won’t look at a previous line to get a figure.

Maybe I can use a checkbox in the Month table to trigger an automation that creates a new record in the Trading Days table on the first of the following month, containing the CF value from the month and a description of “Carried Forward”? Does that make sense?

Here’s the Trading Days table BTW

Sorry. I don’t know why it took this long to click that you’re looking for a running total (I’ve got a lot going on, and in my haste to answer I somehow thought this might be going a different direction). Unfortunately running totals can’t be done via links, lookups, and formulas because they end up causing circular references. This could be done through other means, though: a script in a Scripting app could be designed to do the calculations, and you could run this script as often as desired to update the totals. The downside to this solution is that your ability to use it will depend on your workspace plan. Normally the Scripting app is only available on Pro-plan or higher workspaces, but users on Free and Plus plans can use the Scripting app until September 2021.

Unfortunately I’m not in a position to take on any more clients or I’d offer to help with this, but there are lots of others here who can code this for you. I recommend posting in the #developers:work-offered category to find someone who can assist.

I think I have it, via an automation.
A tickbox in the Months table triggers creation of a new transaction record in the Trading Days table, with “Cash Movement” = Last Month CF, also Linked Month = Month, and Description “Last Month’s Balance Carried Forward”.

The Cash Movement column is now renamed “BALANCE Change”.

An additional column “Monthly Profit/Loss” calculates:
IF(Description=“C/F from last month”,BLANK(),{BALANCE Change}) which gives only the transactions for the actual month, ignoring the added Carried Forward record.

So I now have from each month:

• Profit/Loss on trading (i.e. total income/expenditure for the day)
• Balance at end of month (running total!)

Trading Days view (Transaction Details):

Month View (Summary):

No coding or scripting required! A bit of tidying up required, but it seems to satisfy what I was asked to provide, on the free level. It’s for a charity, so they can’t commit to a monthly/annual fee.