Building a month-over-month output

[Adrienne_Medina]

Hi there AT Brain-trust,

I’m building out a pipeline dashboard using Airtable for my company, and I want to be able to show essentially a comparison of what the data was last month compared to what it is this month.

For example, I would want my dashboard to read something like:

• Number of Opportunities (Current): 1 interest, 3 submitted proposals, 5 won, 2 lost
• Number of Opportunities (As of 30 days ago😞 2: interest, 2 submitted proposals, 4 won, 1 lost

I’m sorted on the current opportunities using the current state of data. I am also aware there are ‘snapshots’ of data that I can retrieve but not download (is that still true?). Even if the snapshot was downloadable, it doesn’t seem to be a very elegant solution because it means creating two sources of data.

Fields I have that might be useful here:

• Date Created (record)
• Date Modified (record)
• Date Status Changed (i.e. when a user updates the opportunity stage, it triggers a date update in this field)

Would really appreciate any tips or workarounds!

[JN_tp · Accepted solution]

If if you want to keep it all in Airtable you could use Airtable automations

• create an automation that triggers every [month]
• you have a table with your data eg 1 in the “interest” field, 3 in “proposals” field
• action of automation is to create a new record in a separate table using the data from the first table.
• Add a created time field to that new table so you can see when the automation created the record and snapshot

[TheTimeSavingCo]

Hm, I feel like this is highly dependent on how your table’s set up, specifically, whether each piece of data exists on the table at all times.

For example, if each opportunity is a single record, and you update the status of the opportunity via a Single Select field, then you would have to create your own historical data so that your dashboard could look it up. That is to say, you would need to create a “History” table of some sort, and every time the Single Select field gets updated, create a record in the “History” table to log its change in status.

I’d be happy to help further, but would need to see how your base is setup to provide more specific solutions

(I’m not sure about the snapshots bit, I’m afraid)