Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

alexandriaIDEA_0-1628016408937.png

 

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!

1 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.

1.png

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.

 

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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. 

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.

1.png

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.