Hello,
I have a table with sales data from 2020 - today. We are trying to figure out if we can use historical pay-up data in order to predict how much of current sales will end up paid. We'd also like to apply historical pay-up rates against forecasted sales. We have an SQL view that includes similar data to the following:
| Date | Order# | Gross Rev | Date Pd | Pd Amount | Refund Amount | Write Amount |
| 1/3/2020 | 20-1 | $ 50.00 | 2/5/2020 | $ 50.00 | $ 50.00 | |
| 1/5/2020 | 20-2 | $ 30.00 | | | | $ 30.00 |
| 1/8/2020 | 20-3 | $ 56.00 | 2/6/2020 | $ 56.00 | $ 25.00 | |
| 1/11/2020 | 20-4 | $ 24.00 | 3/5/2020 | $ 24.00 | | |
| 1/14/2020 | 20-5 | $ 68.00 | 2/15/2020 | $ 68.00 | $ 68.00 | |
| 1/16/2020 | 20-6 | $ 23.00 | | | | $ 23.00 |
| 1/19/2020 | 20-7 | $ 7.00 | | | | $ 7.00 |
| 1/21/2020 | 20-8 | $ 59.00 | 3/31/2020 | $ 59.00 | | |
| 1/23/2020 | 20-9 | $ 22.00 | 1/23/2020 | $ 22.00 | | |
| 1/27/2020 | 20-10 | $ 64.00 | 2/27/2020 | $ 64.00 | $ 35.00 | |
| 1/29/2020 | 20-11 | $ 85.00 | | | | $ 85.00 |
| 1/30/2020 | 20-12 | $ 35.00 | 1/30/2020 | $ 35.00 | | |
| 2/3/2020 | 20-13 | $ 56.00 | 2/15/2020 | $ 56.00 | | |
| 2/6/2020 | 20-14 | $ 47.00 | 3/15/2020 | $ 47.00 | | |
| 2/10/2020 | 20-15 | $ 95.00 | 4/15/2020 | $ 95.00 | $ 25.00 | |
| 2/14/2020 | 20-16 | $ 45.00 | | | | $ 45.00 |
| 2/16/2020 | 20-17 | $ 55.00 | 2/16/2020 | $ 55.00 | | |
| 2/18/2020 | 20-18 | $ 35.00 | 2/28/2020 | $ 35.00 | $ 35.00 | |
The pay-up rate is calculated by dividing total pd by total gross revenue. Any orders still unpaid after 12 months are written off, so I'd like to be able to calculate a pay-up rate on 2020's data (for example) and apply it against sales from the last 12 months to predict how much will be paid. I'd like the date range we use for the pay-up to be dynamic. So, for example, we'd be able to use 2020's pay-up rate and then change the slicer to 2021 to see how the predicted pay-ups change. I have no idea if this is possible and if so, how to approach it. I'm guessing the pay-up rate would have to be saved as a variable? Any guidance is greatly appreciated.
