cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nigel_Mayhew
Helper III
Helper III

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 @Nigel_Mayhew ,

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 @Nigel_Mayhew ,

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.

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 @Nigel_Mayhew ,

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

amitchandak
Super User IV
Super User IV

@Nigel_Mayhew , there is an issue reported for that.

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

 

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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi Amit,

 

And how would it work the previous and following years?

 

Many thanks.

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

Also, vote for the issue.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.