cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

not able to sort by months in chronological order

Hi All,

 

Below is the clustered column chart I am working on.

 

Capture.JPG

 

On the Axis field , I have 2 colums Month ( jan, feb ...)  and Month Number(1,2...).

 

I want to sort the Axis by Month number so that I get (January , Feb ... ) order but I am getting the Alphabetical order. When I try to sort by Month Number, it doesn't show me the option too. Even in Sort by columns on Modelling tab, the option is freezed. I want to show the Axis names as Month but want to sort the Axis by month number. Please let me know if there is a workaround for this.

 

Thanks,

Abhishek 

 

@ankitpatira , @Baskar , @Greg_Deckler

 

1 ACCEPTED SOLUTION

Accepted Solutions
ashishrj Senior Member
Senior Member

Re: not able to sort by months in chronological order

@abhishekpati86 You can also try below for future reference !

 

You can create a calculted column for your date field as follows:

 

~MonthNumber=Month([Created Date])

 

Where MonthNumber is used to extract month number. Then later

 

~Month=SWITCH([MonthNumber],
1,"January",
2,"Feburary",
3,"March",
4,"April",
5,"May",
6,"June",
7,"July",
8,"August",
9,"September",
10,"October",
11,"November",
12,"December",
"Invalid Month Number"

To represents Month in Words

 

~MonthNo=SWITCH([MonthNumber],
1,"01",
2,"02",
3,"03",
4,"04",
5,"05",
6,"06",
7,"07",
8,"08",
9,"09",
10,"10",
11,"11",
12,"12"
)

 

~Year=YEAR([Created Date])

 

~YearMonth=[Year]&[MonthNo]

 

For custom sorting the month column.

Later to sort, you can follow the following step as shown in image below and also the sorted filed

 

original.png

 

You can see the output marked in rectangle. Hope this works!

8 REPLIES 8
Super User
Super User

Re: not able to sort by months in chronological order

I think you are looking in the wrong place for Sort By. See below:

 sortby.png

 


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

Proud to be a Datanaut!


Highlighted

Re: not able to sort by months in chronological order

Hi ,

Capture.JPG

 

Please check, the sort by columns feature is still freezed for me. Let me know if I am missing something.

 

Thanks.

lalthan Regular Visitor
Regular Visitor

Re: not able to sort by months in chronological order

@abhishekpati86, you need to define the "sort by" in the data table, not in the visualisation pane. Also, the column you selected for sorting should be a datimetime or numeric value which understands how the data should be sorted

 

Hope this helps

Re: not able to sort by months in chronological order

ashishrj Senior Member
Senior Member

Re: not able to sort by months in chronological order

@abhishekpati86 You can also try below for future reference !

 

You can create a calculted column for your date field as follows:

 

~MonthNumber=Month([Created Date])

 

Where MonthNumber is used to extract month number. Then later

 

~Month=SWITCH([MonthNumber],
1,"January",
2,"Feburary",
3,"March",
4,"April",
5,"May",
6,"June",
7,"July",
8,"August",
9,"September",
10,"October",
11,"November",
12,"December",
"Invalid Month Number"

To represents Month in Words

 

~MonthNo=SWITCH([MonthNumber],
1,"01",
2,"02",
3,"03",
4,"04",
5,"05",
6,"06",
7,"07",
8,"08",
9,"09",
10,"10",
11,"11",
12,"12"
)

 

~Year=YEAR([Created Date])

 

~YearMonth=[Year]&[MonthNo]

 

For custom sorting the month column.

Later to sort, you can follow the following step as shown in image below and also the sorted filed

 

original.png

 

You can see the output marked in rectangle. Hope this works!

BramS Occasional Visitor
Occasional Visitor

Re: not able to sort by months in chronological order

Hi, 

 

I have a similar issue. I have imported a table with the following columns

 

1. MonthNumber

2. MonthText

3. Year

4. Number values

 

By default the table is sorted by MonthNumber - however, the graph is displayed in alphabetical order of MonthText.

MonthNumber is not part of the graph.

 

How do I fix that the graph is sorted by the MonthNumber and not MonthText?

 

 

 

Re: not able to sort by months in chronological order

Hi,

 

Please go through the below link , it should solve your case.

 

https://community.powerbi.com/t5/Desktop/How-to-sort-month-in-order/m-p/61044#U61044

 

Thanks,

KMPZ Visitor
Visitor

Re: not able to sort by months in chronological order

Hello @ashishrj
I do have the same problem as colleagues earlier, but since I am totally new to the Power BI, can You provide me with step by step guideline where to type these instructions? I am struggling with putting the months in a proper order and I am affraid that without Your help it will be impossible to push further...

 

Important thing: I am trying to visualize monthly sums of rainfall in a 5 year period, so there will be different figures assigned to years and only months identifiers are going to be shared.

 

Bez tytułu.png

 

BTW: Can You give me a tip how to format the dates in excel to be able to import it without any problems to Power BI?

 

Thanks in advance!

K