Forum Discussion
Weeks (52-53) over multiple years.
Weeks 52 and 53 have the potential to be spilt over 2 calender years.
Week 52 for example starts on 27th December 2021 and ends on the 2nd of January 2022.
Hence when i use Week Number as a x axis and filter the visual by Year 2021 week 52 appears on the right (end) of the x-axis like it should.
However if i filter by 2022 week 52 still appears at the right (end) of the x-axis. In this situation you would expect it to appear on the left (the start_ of the x-axis as technically it incorporates the 1st and 2nd of Janaury 2022.
I found this post similar and the x-axis on their visual looks rather neat Solved: Graph showing x-weeks rolling data over multiple y... - Microsoft Power BI Community.
Sadly i couldnt get it to work on my pbix file (problably as im using a different visual and multiple values)
How do i recreate my visual to show the x-axis like the link above OR simply have the week numbers correspond to the Years Correctly
Hi Anonymous ,
I updated your sample pbix file(see attachment), please check whether that is what you want.
1. Create a calculated column as below in Calendar table
YearWeek = VAR _week = IF ( 'Calendar'[WEEKOFYEAR] < 10, "0" & 'Calendar'[WEEKOFYEAR], 'Calendar'[WEEKOFYEAR] ) RETURN IF ( 'Calendar'[MONTH] = 1 && 'Calendar'[WEEKOFYEAR] > 50, ( 'Calendar'[YEAR] - 1 ) & _week, 'Calendar'[YEAR] & _week )2. Replace the field "WEEKOFYEAR" in X axis of chart with the new calculated column just as shown in below screenshot
Best Regards
4 Replies
johnt75Super User
You could create a new column on your date table with year and week number combined like YYYYWW and then use that as the sort column for Week Number.
Thannks johnt75 Attempting to do what you suggest: brings up this prompt ISOWEEKKEY is the YYYYWW column
Sort by Column Error
Hi Anonymous ,
I updated your sample pbix file(see attachment), please check whether that is what you want.
1. Create a calculated column as below in Calendar table
YearWeek = VAR _week = IF ( 'Calendar'[WEEKOFYEAR] < 10, "0" & 'Calendar'[WEEKOFYEAR], 'Calendar'[WEEKOFYEAR] ) RETURN IF ( 'Calendar'[MONTH] = 1 && 'Calendar'[WEEKOFYEAR] > 50, ( 'Calendar'[YEAR] - 1 ) & _week, 'Calendar'[YEAR] & _week )2. Replace the field "WEEKOFYEAR" in X axis of chart with the new calculated column just as shown in below screenshot
Best Regards