I have sales data over several years on several different producsts which is connected to a date table by the sales date. In order to normalize the success rate of sales I want to measure sales not based on when the service was sold but when it would have expired. The thought being the previous sale is a lead that expires in a certain month and the new service doesn't start until the old agreement expires even if the customer purchases before/after the month of expiration of the current service. I figure I need to make three measures but am stumped on how to proceed with the first two. Ultimate goal is to get into a chart for trending analysis.
1st Measure - Getting Leads from Prior Year Sales: Sum sales by product from the prior year based on the expiration date of the service in YYYYMM format.
2nd Measure: Sum all new sales on the effective date of that sale in YYYYMM format.
3rd Measure: Calc the success rate...easy enough to divide Measure 2 by Measure 1
Account | Sold Date | Effective Date | Expire Date | Parent Expire |
1 | 7/10/2021 | 7/10/2021 | 7/9/2022 | 7/9/2021 |
2 | 6/5/2021 | 7/3/2021 | 6/4/2022 | 7/4/2021 |
3 | 7/20/2021 | 7/14/2021 | 7/19/2022 | 7/15/2021 |
6 | | | | 7/9/2021 |
7 | | | | 7/21/2021 |
| Current Month | Measure 1: Leads Expiring | Measure 2: Sales Effective | Measure 3: Success Rate |
| Jul-21 | 5 | 3 | 60% |
