How to get the right weeknumber?

[Dyrham]

When I transform my date table to week number of the year, I get it wrong. The week number is +1 when I do this. How do I handle this?

[sudhav · Accepted solution]

take new column and write a DAX 

[TomMartens]

Hey @Dyrham ,

I have to admit that it's difficult to understand what you mean by you get "wrong week number," this is because more than one valid concept for a week number exists.

As I'm living in Germany, most often, people ask for the ISO week number. This can be achieved by the following DAX code, a column is added to an calendar table:

calendar = 
ADDCOLUMNS(
    CALENDAR( DATE( 2020 , 1 , 1 ) , DATE( 2023 , 12 , 31) )
    , "ISO WEEK" , WEEKNUM( [Date] , 21 )
)

The next picture shows the result:

There is a little challenge with the ISO week number, as a week number sometimes spans multiple years. For this reason, I tend to create a column that contains the year and the ISO week number, a combination of the year of the starting day of a week and the ISO number. The next DAX is exactly doing this. Next to the week number, it contains the start and end day of the week. Both dates are based on the "concept" the start day of the week is a Monday.

calendar = 
ADDCOLUMNS(
    CALENDAR( DATE( 2020 , 1 , 1 ) , DATE( 2023 , 12 , 31) )
    , "SoWDate" , [Date]  - WEEKDAY([Date],2) + 1 
    , "EoWDate" ,  [Date] + 7 - WEEKDAY([DATE],2)
    , "ISO WEEK" , WEEKNUM( [Date] , 21 )
    , "YYYY ISO WEEK" , YEAR( [Date]  - WEEKDAY([Date],2) + 1 ) & " " & WEEKNUM( [Date] , 21 )
)

The final result:

Hopefully, this provides an idea of how to tackle your challenge.

Regards,

Tom 

[Dyrham · Accepted solution]

Hi Tom.

Thank you for your quick answer, I will try it later on. I send you some Kudos even though I haven't tested it yet.

Best Regards 

Dyrham from Uddevalla Sweden

[sudhav · Accepted solution]

take new column and write a DAX