Aggregate values from two tables by month?

[Ashish_Bogawat]

I have two tables - one for income transactions and one for expenses. I’m trying to create a summary table that aggregates values from the two other tables to show monthly balance. Is there a way to:

• Aggregate rows with dates into months?
• Pull in amounts from income and expense tables that map to those months?

[AlliAlosa]

For your first question, I would use a combination of the MONTH(), YEAR(), and DATETIME_FORMAT() functions to group your records by month and year. The best way that I’ve found to do this is to create a formula field with the following formula:

"(" & MONTH(Date) & ") " & DATETIME_FORMAT(Date, 'MMMM YYYY')

Then another field with the formula:

YEAR(Date)

I group the records by {Year} then by {Month}. This way they are all in order by month (otherwise it defaults to alphabetically).

To your second question, I think the simplest way would be to link both your [Income] and [Expense] table to the [Summary] table by month, then use rollup fields to get the monthly balance. The example base below shows what I’m talking about… let me know if I’ve misunderstood what you’re looking for!

Airtable

Monthly Income (Example Base) - Airtable

Explore the "Monthly Income (Example Base)" base on Airtable.

EDIT - If you do the second part of this suggestion, there’s not really a need to do the first part. You could just group by the record linked to the [Summary] table.

[Ashish_Bogawat]

Thanks for that example. It is pretty much what I’m looking for. The only concern is that the Summary doesn’t seem to update as I add entries in Income or Expenses. I need to connect each row to a row in the Summary table manually. Is that something that can be automated?

[AlliAlosa]

If you group your data by the {Summary} field, adding a new record to a particular group should automatically keep the associated linked record (i.e. the month in this example).

Of course, depending on how complicated your scenario is, this might not be ideal. Happy to dive further in if you’d like!

[Janet_Shivell]

Hello Neads,

I have a similar need but can’t seem to solve it, even with your great example. Would you be available to do a screen share at some time?