New column - Same formula?

[Stacy_Carlson]

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?

[W_Vann_Hall]

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

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

precisely your use case.

Be warned: It does take a little time to set up a base to perform calculations of this type — but once it’s in place, it will run indefinitely without your having to cut-and-paste columns or modify formulas each month

Let me know (reply to this reply, so Airtable Community will flag your response to my attention) if you have questions or problems.

---

1. …which, in Airtable, wouldn’t work the way you expect it to, anyway: In Airtable, a column represents a single field as stored in multiple records. Your ‘date/value/value’ example not only includes two different types of data — date and number — it also contains values from 3 different records. A more natural way to model those data would be to have a single record include all department values for a given date.