Displaying Two Different Years in a Column Chart

[Anonymous]

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?

 

[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))

return

UNION(y1,y2)

Hope it helps,

PDG

[P_D_G]

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?

[Anonymous]

@P_D_G I've not tried that - can you share a screenshot or similar so I can see what it would look like?

[amitchandak]

@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

[Anonymous]

@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-msft · Accepted solution]

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,
Kelly

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