Weekly bar chart not sorting by date slicer order between years - 2020 & 2021

[DeonDP]

Hey All

This is an easy one for the clever guys on the forum.

My weekly bar chart always starts at 0 - 52.

So the project I am involved in started in Oct 2020 and is continuing up to today in 2021.

When I select 1/10/20 - 31/3/21 in my date slicer, I get a bar chart that looks like this:

The first couple of weeks is actually 2021 but it looks really wierd.  How do I get the bar chart to start from the date selected in the slicer?

I think it may need to make the change in my DimmDate table by adding a column that uses the correct order taking the week of the calendar year consideration.

This is what I am currently using:

Your help with this one will be appreciated.

[ryan_mayu · Accepted solution]

@DeonDP 

you can use DAX to create a column(not in PQ). you need to modify the DAX since I used my own data

Column = year('Dates'[Date])&"-"&WEEKNUM('Dates'[Date])

 

[ryan_mayu · Accepted solution]

@DeonDP 

you can make 5 to be 05

Column = year(Dates[Date])&"-"&right("0"&WEEKNUM('Dates'[Date]),2)

[DeonDP]

Hi Ryan

Thanks for your reply.  I am having a hard time to create the column.  Do I create it in Power Query or will i be a DAX formula?

I am thinking Power Query - could you please assist with the M Code in Power Query or DAX formula?

Thanks

D

[ryan_mayu · Accepted solution]

@DeonDP 

you can use DAX to create a column(not in PQ). you need to modify the DAX since I used my own data

Column = year('Dates'[Date])&"-"&WEEKNUM('Dates'[Date])

 

[DeonDP]

Hi Ryan

You have answered my initial question - thanks for that, but there is another level that I need help with.  When I add the two values together with DAX, ie: 

to use your graph above: 2014-5 is week 5 and 2014-49 is week 49.  In the viualization however week 5 is displayed after week 49.  I know it has to do with the fact that there is no 0 before the 5, and therefore all 1's, 2's, 3's, etc get sorted by the first number.

How do I get the graph to sort it in proper year-week order.  I have seen a video some time ago where the presenter explains how it's done, but I just cannot remember where I saw it for the life of me.

Maybe just a FORMAT addition to the DAX formula will resolve the problem.

I am fairly new to Power BI, so still have heaps to learn.

Cheers

Deon

[ryan_mayu · Accepted solution]

@DeonDP 

you can make 5 to be 05

Column = year(Dates[Date])&"-"&right("0"&WEEKNUM('Dates'[Date]),2)

[DeonDP]

I am still getting a weird sort order in my graph.

and there should be more weeks as my date slicer indicates:

Could it be because it is text?

[ryan_mayu · Accepted solution]

@DeonDP 

have you tried sort by column and sort asc?

it should work

[DeonDP]

Awesome!!

Thanks for your help Ryan

D

[ryan_mayu]

@DeonDP 

try to create a year-week column(e.g. 201050, 202102) and display that on x-axis.

[DeonDP]

Hey Ryan

Looks like the problem is that

Year Week = YEAR(DimDate[DateValue])&"-"&RIGHT("0"&WEEKNUM(DimDate[WeekofYearNo]),2)

WEEKNUM part of the DAX formula is returning the week of the month and not the week of the year, so all week 4 of the month weeks are bunched together, and so on.

[ryan_mayu]

@DeonDP 

WEEKNUM function (DAX) - DAX | Microsoft Docs

Returns the week number for the given date and year according to the return_type value. The week number indicates where the week falls numerically within a year.

based on the screenshot I provided, you can also see 2013-52 and 2013-53

[DeonDP]

Hi Ryan

DAX formula ended up looking like this:

Year Week = RIGHT(YEAR(DimDate[DateValue]),2)&"-"&RIGHT("0"&(DimDate[WeekofYearNo]),2)

Also had to play around with the sorting to get the graph displaying in the correct order.

Thanks again for your help - much appreciated

Cheers

Deon