How to update a variable in a formula without altering past calculations

I work in a real estate office and have created a base to track sales of all the Realtors for the company. So far I have it set up to automatically calculate commission amounts as well as keep a running total of pending sales volume and closed sales volume. I have tables created for the various transaction scenarios, which are all linked back to an “Agents” table which stores information on each agent, such as their current commission level and a running balance of their sales volume. So far everything is calculating perfectly but the agents are set up on a tiered commission plan which means they have the potential of moving up to a higher level. Right now my tables are set up to calculate a commission based on the current level each agent is at, but if someone moves to the next level, how can I structure it to where it will calculate at the new commission level without changing the sales that were previously entered? Any suggestions are greatly appreciated!!

Hi @Tasha_Klich - this is the closest I can get to this:

Have a “commissions reference” table which notes the commission for one of your realtors and the start and end dates that this commission rate is valid for:

Then, when you note who sold each property don’t assign it to a person, but assign it to a person/commission rate:

You can look up the start and end dates of the person/commission rate and pull these into the property table, which then allows you to check if you have applied the correct rate. See row for Property B where the sold date is after the commission period end date and you get the indicator that the person/commission assignment isn’t the correct one.

This isn’t a perfect solution as I think we would all naturally want to assign a sale to a person and do all the lookups from the name alone, but I don’t think that is possible in Airtable right now in this scenario at least.

Here’s my prototype base of this if you think it will work for you: