Forum Discussion
Displaying Two Different Years in a Column Chart
I expected the new year to bring new issues in my Power BI report.
My column chart displays week number so I've created a new column which displays the week numbers from 202032 (week 32) to 202102 (week 2 2021).
Now my chart is displaying the data in the correct order but I want the axis to just display the week numbers not these messy long ones.
Any ideas?
Hi Anonymous ,
You could create a year column:
Year = YEAR('Table (2)'[Date])Then put the column together in the X-axis,click "expand all" and you will see:
Remeber to click on "sort by Year weeknum" and make "Concatenate Labels" off.
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
- amitchandak
Super User
Anonymous , if you do not disply year week sort will not be correct. You can use Year week as sort column
like YY WW Or WWYY
With the above format you can make YYYYWW as sort column :https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
Also refer :https://www.youtube.com/watch?v=KK1zu4MBb-c
amitchandak When I try to sort week of year by WeekYearNum I get an error because there more than one value in WeekYearNum for the same value in week of year. I'm guessing this is because week 2 can either be 202002 or 202102. Do you know a way around this error?
v-kelly-msftCommunity Support
Hi Anonymous ,
You could create a year column:
Year = YEAR('Table (2)'[Date])Then put the column together in the X-axis,click "expand all" and you will see:
Remeber to click on "sort by Year weeknum" and make "Concatenate Labels" off.
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
P_D_GResolver III
HI Anonymous ,
what if you moved the values into 2 columns (years and week number) and add both of the columns to X axis and drill down to the week level?
P_D_G I've not tried that - can you share a screenshot or similar so I can see what it would look like?
- P_D_G
Anonymous , I couldn't reply directly to the thread above, but here is the answer.
It'd look like something like below. I've used this query to generate the dummy data:
I also renamed the Value column of this table to "Week_Number".
Don't forget to click on the drill down button (red - highlighted) after adding both year & weeknumber columns.
Table =var weeks = GENERATESERIES(1,52,1)var y1 = ADDCOLUMNS(weeks,"Year", 2020, "Bar", RANDBETWEEN(0,500), "Line", RANDBETWEEN(0,1000))var y2 = ADDCOLUMNS(weeks,"Year", 2021, "Bar", RANDBETWEEN(0,500), "Line", RANDBETWEEN(0,1000))returnUNION(y1,y2)Hope it helps,
PDG
