Forum Discussion

Winniethewinner's avatar
Winniethewinner
Icon for Helper IV rankHelper IV
2 years ago
Solved

"synchronized" week number across different years

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. 

"Need Help"
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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