How to define rolling 12 month periods?

[csh8428]

I have a dataset that looks like this

KEY_c	EFF_DT
2269_1_1	5/1/2025
2269_1_1	7/9/2024
2269_1_1	3/21/2024
5523_1_1	5/28/2025
5523_1_1	4/29/2025
5523_1_1	4/19/2024
5523_1_1	4/17/2024

how could I define a 12 month(365 days) rolling period that sequences up for each period where the min EFF_DT for a given KEY_c is the starting point?

Results would look like this

KEY_c	EFF_DT	Twelve Month Period
2269_1_1	5/1/2025	2
2269_1_1	7/9/2024	1
2269_1_1	3/21/2024	1
5523_1_1	5/28/2025	2
5523_1_1	4/29/2025	2
5523_1_1	4/19/2024	1
5523_1_1	4/17/2024	1

[CoG · Accepted solution]

MVPs for this solution involve the DateTimeDiff() function and the Summarize-Join Tools Design Pattern:

1. Summarize to identify the minimum [EFF_DT] for each [KEY_c] then join back to original dataset
2. Compute the DateTimeDiff([Date], [Min_Date], "Year") using "Year" as the time interval, add 1 to reindex
3. You are good to go!

Hope this helps and Happy Solving!