Forum Discussion

decarsul's avatar
decarsul
Helper V
5 years ago

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 tha...
Help appreciated
decarsul's avatar
decarsul
Helper V
5 years ago

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)

 

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?

amitchandak's avatar
amitchandak
Super User
5 years ago

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)

  • decarsul's avatar
    decarsul
    Helper V
    5 years ago

    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?

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      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

       

      • decarsul's avatar
        decarsul
        Helper V
        5 years ago

        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.