"synchronized" week number across different years

[Winniethewinner]

Hi everyone, 

I'm not sure if there is a solution for my problem, I have below the calendar month/week and comparing sales YoY: 

However, the issue is that for some month/weeks in LY, it doesn't exist in CY. For example, April and the 13th week of the year only exist in LY, which is an issue because it'll be showing a very negative YoY.

I'm not sure if there is any solution to create a "synchonized" week number across different years. For example Jan 1-7 will be week1, no matter which year it is.

Thank you. 

[Anonymous · Accepted solution]

Hi Selva-Salimi ,thanks for the quick reply, I'll add more.

Hi @Winniethewinner ,

Regarding your question, since February 2024 has 29 days, there will be problems on April 1st.

Try this.

Column = 
VAR _year = [Date].[Year]
VAR _date = [Date]
VAR _number = MONTH([Date]) *100 + DAY([Date])
VAR _table  = ADDCOLUMNS('Table',"number" , MONTH([Date]) *100 + DAY([Date]))
RETURN 
    
    INT((RANKX(FILTER(_table,[Date].[Year] = _year && [number] <> 229),[Date],,ASC,Skip) - 1) / 7 ) + 1

Best Regards,
Wenbin Zhou

[Selva-Salimi]

hi @Winniethewinner 

I think that you need a modified week numbering. you can write a calculated column as follows:

week_number := var _days = calculate (count ('date' [dateID]) , filter ('Date' , 'Date' [dateID] <= earlier ('date' [dateid]) && 'Date' [year] = earlier ('date' [year] ) )) / 7

return roundup (_days , 0 )

then you should use this column in your calculations and visualizations.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.