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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
decarsul
Helper V
Helper V

Weeknum wrong

Gday all,

 

As some / most here have already stated / noticed, for us europeans (according to iso 8601) the weeknumber for 2021 is wrongly calculated. In fact, the thing that worries me more is that according to DAX a week can 'change' number in the middle of it 'being'.

 

When applying multiple workarounds stated here, it is not a permanent sollution, in fact 2022 has the same issue. To me this is a bug that deserves a proper fix. Without creating alternative columns and tables to mitigate the issue and make it appear right. What would be a code for a permanent fix, aside of MS actually treating this as a bug?

7 REPLIES 7
amitchandak
Super User
Super User

@decarsul , The information you have provided is not making the problem clear to me. Can you please explain with an example?

 If this is an issue, it can be reported to the issue. But first, share details of what is wrong?


Appreciate your Kudos.


Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

I'm sorry i wasn't clear. Let my try to elaborate.

 

Currently, DAX code 'weeknum(date;1)' shows that week 1 starts on Jan 1st and week 2 starts on Jan 3rd of 2021. While Jan 1st is a friday, making that particular week both week 53 AND 1. Which should not even be possible to begin with. (why else define where the week starts, if the code is going to completely neglect it anyway)

decarsul_0-1609842609620.png

 

So not only does it count week 2 wrong, it starts counting week 1 halfway a week, totally neglecting the startday of a week. Which really tells me that it is actually basing the weekcount based on the year and not actually a date time format.

 

The question is now. How is it even possible that weeknum cuts a week in half and decides to give it 2 values? The followup question would be, how can we fix that, structurally?

The third question would be, what would be a proper DAX code without an IF statement to correct each and every year seperately, that we / i can use to correctly display week numbers?

@decarsul , I think power bi changes week with year, so your week 52/53 or week 1 can smaller weeks.

 

In case you do not need that you might have build custom calendar (Date table)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Not entirely sure what to do or how to interpret your answer.

It is DAX code messing up in my opinion, as you stated, its starting a new week with a new year. Regardless the fact that you state a week should start on sunday or monday. What is the point of that argument then in this case?

@decarsul , You can log an issue or Idea

Issue - https://community.powerbi.com/t5/Issues/idb-p/Issues

Idea - https://ideas.powerbi.com/ideas/

 

As of  now, you can create your own column

 

//Monday Week start

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

 

//Sunday Week Start

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)

 

min week start of year = minx(filter('Date',year([Week Start date])=earlier(year(Week Start date]))),[Week Start date])
week No = quotient(datediff([min week start of year],[date],day),7)+1

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Thanks for the suggestion.

However, i've reverted and am still testing to Weeknum(date,21).

So far, this seems to be working. With the exception that week 53 of year 2021 is at the end of each visual, instead of the start.

Hi @decarsul ,

 

When you say "week 53 of year 2021 is at the end of each visual, instead of the start", I think you mean the first three days of 2021 belonging to week 53 of year 2020.

 

If this is the case you can get them at the top of your visual tagging them as 2020 instead and sorting by Year Week (2020-53 would come first than 2021-01).

 

ISO Week number:

 

Payeras_BI_0-1611236752206.png

 

ISO Year:

 

Payeras_BI_1-1611236790178.png

 

Regards,

 

 

 

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors