Hey guys,
I need some help coming up with a calculated column. I have two tables...
a calendar table (has contiguous dates):
Date Exercise
| 1/1/2020 | |
| 1/2/2020 | |
| 1/3/2020 | |
| 1/4/2020 | FY20 Exercise 1 |
| 1/5/2020 | |
| … | |
| 3/1/2020 | FY20 Exercise 2 |
| … | |
| 12/31/2020 | |
| 1/1/2021 | |
| 1/2/2021 | FY21 Exercise 1 |
| … | |
and a fact table (does not have contiguous dates):
Exercise Date PersonID
| FY20 Exercise 1 | 1/4/2020 | 1 |
| FY20 Exercise 2 | 3/1/2020 | 1 |
| FY20 Exercise 2 | 3/1/2020 | 2 |
| FY19 Exercise 2 | 3/1/2019 | 3 |
| FY21 Exercise 1 | 1/2/2021 | 3 |
these are related on the Exercise column.
What I need to do is create a calculated column in my fact table indicating via a flag if a PersonID showed up at least twice in exercises within a rolling 12 month period. The end date of the twelve month period would be the date of the most recent exercise they participated in. The start date of the period would be the most recent exercise in which they participated that is within 12 months prior of the end date exercise.
so for example PersonID 1 participated in two exercises within a 12 month period. PersonID 2 only participated in one.
Person ID 3 participated in two exercises, BUT NOT in a rolling 12 month period.
Output would look like:
Exercise Date PersonID IsRepeat
| FY20 Exercise 1 | 1/4/2020 | 1 | 1 |
| FY20 Exercise 2 | 3/1/2020 | 1 | 1 |
| FY20 Exercise 2 | 3/1/2020 | 2 | 0 |
| FY19 Exercise 2 | 3/1/2019 | 3 | 0 |
| FY21 Exercise 1 | 1/2/2021 | 3 | 0 |
