I have a table that has details of workers by date. I've summarized it by worker by quarter and their profession. I have a distinct count for each worker having worked by quarter. I need to be able to calculate the retention if they worked prior quarter and prior year. So if they worked prior year and did not work in the current year they are considerd not retained. If they come back and work they the are reset. I've tried to show you how I calculate it manually. I need help to only track those that were working in the previous year or previous quarter, then have a precentage. (current period / prior period). Pleae let me know if you have any ideas. Thank you.
| Quarter | WorkerID | Count | Profession | |
| 2021-Q4 | 1 | 1 | XYX | | |
| 2022-Q1 | 1 | 1 | XYX | | |
| 2022-Q2 | 1 | 1 | XYX | | |
| 2022-Q3 | 1 | 1 | XYX | | |
| 2022-Q4 | 1 | 1 | XYX | | |
| 2021-Q4 | 2 | 1 | ABC | | |
| 2022-Q1 | 2 | 1 | ABC | | |
| 2022-Q2 | 2 | 1 | ABC | | |
| 2021-Q4 | 3 | 1 | XYX | | |
| 2022-Q1 | 3 | 1 | XYX | | |
| 2022-Q2 | 3 | 1 | XYX | | |
| 2022-Q4 | 3 | 1 | XYX | | |
| 2021-Q4 | 4 | | ABC | | |
| 2022-Q4 | 4 | 1 | ABC | | |
| 2022-Q4 | 5 | 1 | ABC | | |
| 2021-Q4 | 6 | 1 | XYX | | |
| 2022-Q3 | 6 | 1 | XYX | | |
| 2022-Q4 | 6 | 1 | XYX | | |
| | | | | | |
| | | | | | |
| | 2021-Q4 | 2022-Q1 | 2022-Q2 | 2022-Q3 | 2022-Q4 |
| XYX | | | | | |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 3 | 1 | 1 | 1 | | |
| 6 | 1 | | | 1 | 1 |
| ABC | | | | | |
| 2 | 1 | 1 | 1 | | |
| 4 | 1 | | | | 1 |
| 5 | | | | | 1 |
| | | | | | |
| Distinct Count | | | | |
| XYX | 3 | 2 | 2 | 2 | 2 |
| ABC | 2 | 1 | 1 | | 2 |
| | | | | | |
| Retention Count | | | 2022-Q4 | 2022-Q4 |
| | | | | Prior Qtr | Prior Year |
| XYX | | | | 1 | 1 |
| ABC | 1 | | | | 1 |
| | | | | | |
| Retention Calculation | | | Percent |
| XYX | | | | 50% | 33% |
| ABC | | | | 0% | 100% |
