Forum Discussion

Fidzi8's avatar
Fidzi8
Helper V
5 years ago
Solved

Wrong calender week

Hi,
my report show me wrong number of week by the calendar.

 

Why I have second week but in a paper calender is first week of year? 

Can I fix this?

Thank you
Ondřej

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Fidzi8 , You have to create a customer week

    like these columns in date table

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 // or use WEEKDAY('Date'[Date],1) for Sunday week

    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
    Week Start Year = minx(filter('Date',[Year] =earlier([Year])),[Week Start date])

     

     

    There are a couple of issues logged on this in last week, as this does not match with other Microsoft calendar in other apps

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Fidzi8 , You have to create a customer week

    like these columns in date table

     

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 // or use WEEKDAY('Date'[Date],1) for Sunday week

    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
    Week Start Year = minx(filter('Date',[Year] =earlier([Year])),[Week Start date])

     

     

    There are a couple of issues logged on this in last week, as this does not match with other Microsoft calendar in other apps

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

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi Fidzi8 ,

     

    Create a column as below:

    weeknum1 = 
var _firstday=CALCULATE(MIN('Table 2'[Date]),FILTER('Table 2',YEAR('Table 2'[Date])=YEAR(EARLIER('Table 2'[Date]))))
Return
IF(WEEKDAY(_firstday,2)<>1,WEEKNUM('Table 2'[Date],2)-1,WEEKNUM('Table 2'[Date]))

    And you will see:

    For the related .pbix file,pls see attached.

     

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    possible to get the week number written most nicely?

     

    how do i combine these two formulas?

    This (the format i want)

     

    YearWeek Column = IF(WEEKNUM([Posting Date])<10,FORMAT([Posting Date],"YYYY-0WW"),FORMAT([Posting Date],"YYYY-WW"))
     
    with this
     
     
    weeknum1 =
    var _firstday=CALCULATE(MIN('Calender table'[Date]),FILTER('Calender table',YEAR('Calender table'[Date])=YEAR(EARLIER('Calender table'[Date]))))
    Return
    IF(WEEKDAY(_firstday,2)<>1,WEEKNUM('Calender table'[Date],2)-1,WEEKNUM('Calender table'[Date]))