Forum Discussion

Fidzi8's avatar
Fidzi8
Icon for Helper V rankHelper 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

     

Anonymous's avatar
Anonymous
Not applicable
5 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]))