Assigning week number to multi-year rolling week id's

[Nsantana]

I have weeks for multiple rolling Fiscal Years and need to assign them week number based on the rolling weeks. 

I pulled weeks for the last 4 years ending 202431 (July 2024) and need that last week to be week 52 and then the numbers should restart after 52 weeks for the previous years. So 4 groups of 52 weeks. 

Please and thank you.

Fiscal Week ID    Week ID

202431                 52
202430                 51
202429                 50
202428                 49
.

.

202331                 52

202330                 51

[apathetichell]

What you are requesting is not possible without knowing the exact date which week 52 ends - and any rules requiring day of week for week start/end.

[Nsantana]

Really? I was thinking it's just labeling the rows till 52 and then then numbers would repeat. I was wondering if something like the Tile Tool would work?

[apathetichell · Accepted solution]

let me edit this - are you saying you have an ordered set of week values that you want to bin into sets of 52 - if so use- mod([RowCount]-1,52)+1 if you are looking to dynamically generate the week start dates - you have to know a beginning or end day which you are looking at.

[Nsantana]

PERFECT. Thank you!