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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Week number

Hi all,

 

How can I achieve the following week number results?:

 

DayDateISO8601

Special

Thursday31.12.20205353
Friday01.01.2021531
Saturday02.01.2021531
Sunday03.01.2021531
Monday04.01.202111
Tuesday05.01.202111
Wednesday06.01.202111
Thursday07.01.202111
Friday08.01.202111
Saturday09.01.202111
Sunday10.01.202111
Monday11.01.202122

 

Looking forward to your answers.

 

Thanks.

1 ACCEPTED SOLUTION

Hi @Anonymous ,

You can create the calculated column like this:

ISO8601 = WEEKNUM('Table'[Date],21)

Please refer the [ISO] column in my sample file from the previous post.

 

Best Regards,
Community Support Team _ Yingjie Li
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

6 REPLIES 6
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

Based on your description, seems like you want to define 10 days as a week for each year, you can create a calculated column like this:

Special =
ROUNDUP (
    DIVIDE (
        DATEDIFF ( STARTOFYEAR ( 'Table'[Date].[Date] ), 'Table'[Date], DAY ) + 1,
        10
    ),
    0
)

week.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hello,

 

Thank you for that.

 

How can I get the week numbers to calculate based on the ISO8601?:

 

DayDateWeek Number ISO8601
Thursday31.12.202053
Friday01.01.202153
Saturday02.01.202153
Sunday03.01.202153
Monday04.01.20211
Tuesday05.01.20211
Wednesday06.01.20211
Thursday07.01.20211
Friday08.01.20211
Saturday09.01.20211
Sunday10.01.20211
Monday11.01.20212

 

Looking forward to hearing back from you.

 

Best regards.

Hi @Anonymous ,

You can create the calculated column like this:

ISO8601 = WEEKNUM('Table'[Date],21)

Please refer the [ISO] column in my sample file from the previous post.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , there is an issue reported for that.

https://community.powerbi.com/t5/Issues/DAX-WEEKNUM-gives-incorrect-values-since-jan-1st-2021/idi-p/1583824

 

For 2021, try like new week column like

Week Number =

if(Year([date] = 2021 && WEEKNUM([Date],2) >=2, WEEKNUM([Date],2) -1,WEEKNUM([Date],2))

Anonymous
Not applicable

Hi Amit,

 

And how would it work the previous and following years?

 

Many thanks.

@Anonymous , You have to tell the logic, I can give that.

Also, vote for the issue.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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