WeekNum sequence issue

[h_l]

Hi there,

I created a Date table as following:

The weekNumber dax is:

WeekNum = "Wk" & WEEKNUM('Date'[Date])

However, the weekNumber does not work well in chart sequence.

The actual sequence is:

2020 wk1, then wk 10, 11,....wk19, wk2, wk20,21...wk29, wk3, wk30....

I think it is because text "wk" is added in front.

Is there a way to make it correctly as wk1, wk2, wk3....wk10, wk11..., wk19, wk20, wk21?

Thanks for the help.

H

[richbenmintz]

Hi @h_l ,

create a column called Wk as 

WEEKNUM('Date'[Date])

then set the 'Sort By Column' of the weekNumber field to your newly created Wk field.

you will find the property in the properties pane or in the columns ribbon

thanks

[h_l]

Hi richbenmintz,

I've set it. but as you can see, "wk10" is the one after "wk1", but not "wk2" after "wk1"

Did I set it correctly?

Thanks.

H

[richbenmintz]

Hi @h_l ,

That is the correct setting, but you need to sort by the weenknum as a number, the string wk3 comes after wk29

[h_l]

Thats right richbenmintz, so my question is, is there a way to make the weekNum in the correct sequence while keeping "wk" prefix. So that in the chart I can see axis like: 2020 wk1, 2020 wk2 instead of 2020 1, 2020 2

[richbenmintz]

Hi @h_l ,

create column that is the week as an int

WEEKNUM('date'[Date])

then set the sort by column to the weekNum as int column

[h_l]

Hi  @richbenmintz ,

When I create a column and set to int, it is fine.

But once "wk" prefix is added, the sequence is wrong again.

So my question is, is there a way to keep the prefix "wk" while keeping the correct sequence, 1, 2, 3?

I want to display in visual as: 2020 wk1, 2020 wk2, but not 2020 1, 2020 2.

Please let me know if I was in wrong setting. New to Power BI.

Thank you.

H

[richbenmintz]

Hi @h_l ,

create a second colunm with only the week as int, then set the sort by column of the WeekNum field as the new field with only the week as an int in it. The point of the sort by column is to assign the sort order by another attribute

[h_l]

Thanks for prompt reply @richbenmintz , if create a column keep the int weekNum only (e.g 1,2,3,4,5,6) , then the question becomes to: how to make it display as "wk1", "wk2" in a visual axis?

Any suggestion?

[richbenmintz · Accepted solution]

Hi @h_l ,

you need two columns, one with the week as an int, the other as the week as a string, then you will sort the week as a string by the week as int. see image below

[h_l]

Got it! Thank you!!

[richbenmintz]

No Probs!

[h_l]

Hi @richbenmintz 

opps...sorry, come again,

I created intWeekNum in "Date" table,

the visual is using data from "General Performance" table.

So although I choose "sort by" is "intWeekNum" in table "Date", but the visual is not using it.

In the visual, there is no place to choose sort by "intWeekNum"

Any further settings needs to be changed?

Thanks in advance.

H

[richbenmintz]

Hi @h_l 

Did you choose the week as it field as the sort by column for your weeknum field? in your image it looks like you set the sort by column of the intweeknum column and not the weeknum column. If you look at the solution provided, I have selected the weeknum column and set the sort by column property for that column.

[h_l]

Appreciate your patience👍 to a new comer to power BI !🤣

[richbenmintz]

Hi @h_l ,

Glad to help out!