Sorting week is not working

[VPonnarath]

Hello,

I have created a calculated column for the "Week Name-Month" in my date table which display the Month and the week number of the particular month. 

Week Name-Month = CONCATENATE('Date'[Fiscal Month],CONCATENATE("- Week",'Date'[Week Number of Month]))

I am trying to use Week Name-Month column in my Line chart Axis and it is working, but we have a problem in sorting . Its not getting sorted in the order of the month, it sorts in a wired way. I tried t use the "Sort By Column" Functionality and tried sorting against Week number of the month. But, that dosen't work . 

Can anyone please help me with this? 

Thanks.

[parry2k]

@VPonnarath you are on right track to using "sort by"

What is your fiscal month? Is it "Month name" or "Month Number"? Can you share screen shot how your week and sort column looks like?

[peter123]

Hello,

Looking for some help and it seems you have some good experience with this.   I have a Line Chart that shows revenue for current year and prior year with the axis as Month Name and Week Ending date (ie Apr-04, Apr-11, etc.).   I cannot get it to sort properly and I've tried to sort that table column my many other columns.  I get the error message below and I've tried to sort by columns with various data structures that should satify the error.   Please help! lol 

"We can't sort the 'WeekEnding' column by 'YearWeekSort'. There cant be more than one value in...."

[VPonnarath]

Thank you for your response ! 

See the below screen shot. I am trying to sor the "Week Name -Month " Cloumn with "Week Number of Month" Column. 

In my Line chart when I use Week Name -Month on Axis, It is not dispaying in an order

Line Chart- See the week order displaying

[parry2k]

i think your sort column should be

MM-Week Number of Month, like 

01-01

01-02

01-03

...

12-01

12-02

12-03

And use this new field to sort your "Week Name-Month" field

[VPonnarath]

Hello, Actually, that didnt work. 🙂

When i was testing with my sample data, it was working for me. But, when we uploaded the real data for whole year, the week name is not sorting in an order. 

Can you please give me another solution for this?

Thanks, 

[parry2k]

ok so what is the issue. I guess you need to add more value in sort column but if you provide more details it will be helpful to resolve the issue.

thanks,

p

[VPonnarath]

Okay. I will try to explain as I can.

I am trying to bring the week name ( ex: Jan-Week 1, Jan-Week2) in a line chart X axis. 

I have date table with , Week number of Year, Week Number of Month columns . I created a calculated column for dispaying the week number in a more understandable way , ie..  Jan-Week1, Jan -week2 . Below is the dax I am using for this. 

Week Name-Month = CONCATENATE('Date'[Fiscal Month],CONCATENATE("- Week",'Date'[Week Number of Month]))

I need to sort this cloumn like below

Jan-Week1

Jan-Week 2

...

...

..

Feb-Week1

Feb-Week 2

Uploading the pbix with date table here.. pbix

[v-yuezhe-msft · Accepted solution]

@VPonnarath,

Please update your formula to the following, then change data type of week sort column to whole number.

Week Sort Column = CONCATENATE('Date'[Calendar Month Number],'Date'[Week Number of Month])

This way, when you sort Week Name-Month column by Week Sort Column, you will get expected sort.



Regards,
Lydia

[VPonnarath]

BRILLIANT !! 

That worked like a charm.  Thank you so much  !! 🙂