ISO 8601 Week of Year Numbering - Change Start of Week to Sunday

[Anonymous]

I have the following issue - I know how to convert dates to ISO 8601 Week of Year numbers (in order for the year numbering to begin with the week containing the first Thursday of the year) with = WEEKNUM('Table'[Date Only], 21)

This works just fine with my data, however, we'd still like to use the U.S. way of Day of Week numbering with SUNDAY beginning the weeks rather than Monday. Is this possible?

Here is an example of what I mean visually:

ISO 8601 Week 53 (2019) and Week 1 (2021) with Mondays as the first day of the week (the original way)

		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
Week 53		12/28/2020	12/29/2020	12/30/2020	12/31/2020	1/1/2021	1/2/2021	1/3/2021
Week 1		1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	1/9/2021	1/10/2021

ISO 8601 Week 53 (2019) and Week 1 (2021) with Sundays as the first day of the week (the way we'd like it)

		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
Week 53		12/27/2020	12/28/2020	12/29/2020	12/30/2020	12/31/2020	1/1/2021	1/2/2021
Week 1		1/3/2021	1/4/2021	1/5/2021	1/6/2021	1/7/2021	1/8/2021	1/9/2021

Thank you in advance for any assistance!

[parry2k]

@Anonymous if you don't give the 2nd parameter to WEEKNUM function it will start from Sunday, not sure if that is what you are looking for.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Unfortunately, it's not. I want to use the European way of week numbering (ISO 8601) that doesn't start Week 1 of the year with January 1st. 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Try to  build a new weeknum column and year by dax code.

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2021, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "WeekNum", WEEKNUM ( [Date], 1 ),
    "WeekDay", WEEKDAY ( [Date], 1 ),
    "MonthName", FORMAT ( [Date], "MMMM" ),
    "DayName", FORMAT ( [Date], "DDDD" )
)

Here I have a date table from 2019 to 2021, if we use weeknum function directly we will get weeknum =1 in the begain of a year. Try this code.

ISO 8601 WeekNum = 
VAR _COUNT0 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[WeekNum] - 1 = 0
        )
    )
VAR _BASENUM1 =
    IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
VAR _ISO_8601_WeekNum =
    IF (
        WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1
            && 'Date'[Year] = 'Date'[Year]
            && _BASENUM1 = 0,
        WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1,
        _BASENUM1
    )
RETURN
    _ISO_8601_WeekNum

Text Week = 
COMBINEVALUES(" ","Week",'Date'[ISO 8601 WeekNum]) 

WeekYear = 
VAR _COUNT0 =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            'Date'[Year] = EARLIER ( 'Date'[Year] )
                && 'Date'[WeekNum] - 1 = 0
        )
    )
VAR _BASENUM1 =
    IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
RETURN
IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])

Result is as below. I only show week1 and week52 in my matrix.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.