Forum Discussion
Helper IIITimeline Slicer - 2017 with 53 Weeks
Community ChampionI think the disconnect here is that may be how you assign weeks numbers within your organization, but that is not how the WEEKNUM function within Power BI works. You can understand the issue here. It would be difficult to account for how all organizations assign week numbers within their own calendar because I've seen some crazy ways in which companies do this, especially when working with a non-standard calendar like June-June or Feb-Feb, things like that. Bottom line here is that if your week numbers do not line up with the 2 options that WEEKNUM provides, you probably have a couple options:
- Contact the developer of the visual and see if you can get them to code in some kind of exception for your circumstance
- Create your own version of the visual that works the way you want it to work
- Create your own week num column and tie some kind of slicer to that
Bottom line is that this is not an inconsistency within Power BI, it just doesn't work the way you want to see the week numbers shake out.
I think it's because the American method of week numbering differs from the ISO / European week numbering method. We over here in Europe, normaly use Monday as the start of the week and in case January 1 is on Monday, Tuesday, Wednesday or Thursday, that week is the 1st week of the next year. In case January 1 falls on Friday, Saturday or Sunday, the weeknumber is increased +1 with the previous weeknumber of last year. This is different from the "USA"-method, where the first week of the year depends on what day January 1 falls: in case it's a Sunday, PowerBI calculates week 1, otherwise it calculates a next weeknumber of the previous year for that week.
Outlook uses two settings to solve this:
- First day of week (in USA: Sunday, but in Europe, according to ISO weeknumbers: Monday)
- First week of year (in USA: Starts on January 1, but in Europe, according to ISO weeknumbers: First 4-day week).
In my opinion, the Timline slicer should be extended with the First Week Of Year setting to solve this issue of incorrect week numbers.
When you set the week start in the Timeline slicer to Mondays, the weeknumber looks correct for European weeknumbers in 2017 and 2018, but in that setting 2018 has 53 weeks instead of 52 because January 1 2019 starts on a Tuesday.
So I guess the Timeline slicer needs to be extended with the First week of year setting, like in Outlook.
- Greg_Deckler
ratercero - While not documented, you can potentially use 21 as the last WEEKNUM parameter to force 52 weeks.
WeekNum21 = WEEKNUM([Date],21)