cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fidzi8
Helper IV
Helper IV

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? 
datum.png

Can I fix this?

Thank you
Ondřej

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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/...

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

3 REPLIES 3
kolovez
Helper I
Helper I

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]))
v-kelly-msft
Community Support
Community Support

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:

v-kelly-msft_0-1610525455420.png

For the related .pbix file,pls see attached.

 

 

 

Best Regards,
Kelly

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

amitchandak
Super User
Super User

@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/...

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.