How to calculate custom week number which resets for every 12 weeks?

[ippman]

How to calculate a custom week number that starts from a specific date (in my case it's 2/28/2022) and resets the number to 1 after the 12th week. Your assistance in this matter will be greatly appreciated.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

WeekNumber Reset every 12 CC =
VAR _yearnumber =
    YEAR ( 'Calendar'[Date] )
VAR _weeknumber =
    WEEKNUM ( 'Calendar'[Date], 21 )
VAR _maxweeknumberwithinthesameyear =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                'Calendar',
                "@yearnumber", YEAR ( 'Calendar'[Date] ),
                "@weeknumber", WEEKNUM ( 'Calendar'[Date], 21 )
            ),
            [@yearnumber] = _yearnumber
        ),
        [@weeknumber]
    )
VAR _sequencenumber = _yearnumber * _maxweeknumberwithinthesameyear + _weeknumber - 105152
VAR _result =
    MOD ( _sequencenumber, 12 )
RETURN
    IF ( _result = 0, 12, _result )

[ippman]

Hi Jihwan_kim,

I'd like to thank you very much for taking the time to respond with a solution to my question. 

As shown in the image below you have provided a solution that is almost close to what I'm looking for. I have noticed one issue where the week number should 8 instead of 12 for the date 01/01/2023. Also, could you please explain as to why the # 105152 was used in the calculation? 🤔

[Jihwan_Kim · Accepted solution]

Hi,

thank you for your feedback.

while I was trying to find mistakes in the formula, I reallized that this formula only works when the periods are not exceeding to have week number 52. If there is week number 53 involved, I think it will not work. 

Sorry to say that I did not consider this from the beginning.

By the way, when creating a calculation, "sequencenumber = yearnumber X weeknumber + weeknumber", it starts from 10513.

So, I wanted the sequence number to start from 1, and this is why I used the number 10512.

If your calendar table does not include the weeknumber53, the below formula is working, however, if it is including weeknumber53 somewhere in the period, please use the [Second one CC] column that is described in the attached file.

WeekNumber Reset every 12 CC = 
VAR _yearnumber =
    YEAR ( 'Calendar'[Date] )
VAR _weeknumber =
    WEEKNUM ( 'Calendar'[Date], 21 )
VAR _sequencenumber =
    IF (
        MONTH ( 'Calendar'[Date] ) = 1
            && _weeknumber > 50,
        ( _yearnumber - 1 ) * 52 + _weeknumber - 105152,
        _yearnumber * 52 + _weeknumber - 105152
    )
VAR _result =
    MOD ( _sequencenumber, 12 )
RETURN
    IF ( _result = 0, 12, _result )

[ippman]

Your solution is rock-solid now. 💪

My deepest thanks for your swift response, support and guiding me through this task. I sincerely appreciate the time you spent finding a solution for me. 🙏

I hope you won't mind if I ask one last assistance on this task. It has come to my attention that my company is considering resetting the week number to 1 starting from Jan 2, 2023 and continue with the 12-week pattern as before. I tried my best to use your logic as a basis for figuring this out, but I wasn't successful. Please advise.

[Jihwan_Kim]

Hi,

I added two more columns in the attached file in order to calculate the result (Weeknumber reset V2) that starts from Jan 2nd 2023.

Thank you.

[ippman]

Works like a charm. Thank you very much, Sir !