# New column - Same formula?

Topic Labels: Formulas
1345 1
cancel
Showing results for
Did you mean:
6 - Interface Innovator

I’m trying to bring in a spreadsheet into a Base.

Each month, we pull a report that tells us how many people, per department, we have.
Department 1 = January '19 has 32, December '18 has 28, November '18 has 34
Department 2 = January '19 has 88, December '18 has 64, November '18 had 100
etc.

We create a column for each month and put that data in the column
11/2018
32
88

Next column
12/2018
28
64

Next column

1/2019
34
100

We do this every single month, so that means we are adding a new column with the newest data.

Issue #1
I need to calculate the difference between the current month - past month.

Column 1/2019 - Column 12/18

I then need to calculate the difference between the current month - 2 months prior

Column 1/2019 - Column 11/2018

How do I do this, knowing that the column is going to change every single month when we add the newest month column?

Issue #2
I need to create filters that show me entries that increase and decrease, based off the formulas created above.

Is this even possible?

13 - Mars

With Airtable — or any RDBMS — whenever your process says "Every [blank days], we enter new [blank],’ you should immediately think of using a linked record for each of those [blanks].

In this case, in your `[Main]` table, you would define a linked-record field that links to the `[Monthly Personnel Reports]` table. In turn, `[Monthly Personnel Reports]` would have the following fields:

• `{Month}` - the primary field, which could be a single-line text field in which you enter the month of the report in ‘`MM/YYYY`’ format.
• `{Dept 01 Headcount}` - Integer number field.
• `{Dept 02 Headcount}` - Integer number field.
[…]
• `{Dept ## Headcount}` - Integer number field.

Now, each month, instead of creating a new column.¹ you would click on the ‘`+`’ sign in the `{Monthly Personnel Reports}` field, create a new linked-record, and populate it with each department’s headcount.

That’s the easy part.

Actually, performing the ‘this v last’ and ‘this v last-plus-one’ reports isn’t all that difficult — but explaining how to do them is. So I’m going to pass the ball — to myself. Here is a previous post discussing multi-record calculations, which is what you want to do. That post includes a link to a base demonstrating four types of multi-record calculation; one of the demonstrations is

1. Retrieving a value from the previous and previous-but-one records.