Overview per month MMR calculation

[Roy_Selbach]

Hello,

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

[Justin_Barrett]

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:

Loom

Automatic linking of customers to month records

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.