Calculating Monthly Sales Commissions


#1

Looking for an effective way to calculate and track sales commissions without having to resort back to using Excel.

I have 1 Table with a list of all clients (populated with zapier when a sale is made). With a linked column to another table for the Sales Person who made the sale. I also have a date field for when the sale was made.

Clients are on a monthly subscription and sales commission is paid for every month they stay a client.
For example…
Month 1-3 = $100.00
Month 4-12 = $40.00
Month 4 Bonus = $100.00

My thought initially how this would flow (No Idea How to Actually do this in Airtable) was to some how calculate what Month the client was currently in, lookup what the rate is for that month and apply a basic calculation. Then I could round up all the commissions that would be due for the week. I would then need to track whether or not the commission was paid out or not.

Any thoughts are guidance would be much appreciated.