I am trying to track sales velocity for our sales reps. I have a Sales Representitive table, a Leads table and an Opportunity table. When a Sales Rep gets a sale, we enter in a "Date Closed" date in the leads table and move the lead from the Leads table to the Opportunities. Sales Reps earn more money based upon sales velocity.
Here is my issue. I have created a summary field (Sales Representitive table to Lead table) that counts "Date Closed" for the past 30 days, which tells us how many sales the rep has closed within the last 30 days. I am then doing a lookup on the summary field into the opportunities table and I have a dynamic form rule in the opportunities table that captures the number counted by the summary field for the running 30 day sales velocity the day that it closes. My issue is when a sale cancels, I am unable to figure out a way for the corresponding records to update so that we don't overpay a sales rep on jobs that should be adjusted due to a prior cancellation. The problem with the summary field is that it is counting all records with the "Date Closed" after 30 days in the past which means it is constantly moving forward.
Essentially I need a formula field that I can create in each record that looks 30 days in the past from the date of the record and not the current date such as the summary field does. Is this possible or is there another work around to better track real time sales velocity that also takes into consideration any prior jobs (records) that may have canceled in the past?