Overview per month MMR calculation

Topic Labels: Formulas
953 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer


So far a big fan of the product, however I am a bit puzzled how to tackle this problem.
I have a customer list which are either ‘active’ or ‘in-active’; based on their plan.

I would like to make an overview which automatically calculated the monthly recurring revenue from people with an active plan. Currently I set manually the active & in-active date.

So my logic was as followed:

January 2021: IF ‘month= 01’ & ‘year= 2021’ is WITHIN ‘active date’ AND ‘in-active date’ THEN SUM ‘Monthly recurring revenue’

To add would like to have also a list of the amount of active customers:

January 2021: IF ‘month= 01’ & ‘year= 2021’ is WITHIN ‘active date’ AND ‘in-active date’ THEN COUNT ‘Customers’

As I am not an experienced Airtable user; would like to know if someone can help me. Since tried a few things; but I seem not to find my way

1 Reply 1

Welcome to the community, @Roy_Selbach! :grinning_face_with_big_eyes: You say that you have a list (guessing in a table) of customers, but you didn’t describe very clearly where/how you want to see and interact with this aggregation of revenue and active customer data. One option would be to have a second table where each record represents a single month (I’ll call this the [Months] table). Customers would be linked to the month records that correspond to their active range. With that done, each month record would be able to show a summary of all revenue from the linked customers, as well as a total count of those customers.

The tricky part is figuring out a way to automatically create those links between customers and months. I wasn’t sure if it was possible without resorting to code (and I do love me some code!), but I found a way to make it work with a couple of no-code automations and a handful of helper fields. I don’t have time at the moment to type out the setup of the system, but here’s a look at how it works:

If this will work for your use case, let me know and I’ll work on writing out the details of how to put it all together.