Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhishekpati86
Helper III
Helper III

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

@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!

View solution in original post

9 REPLIES 9
BramS
New Member

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?

 

 

 

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,

Greg_Deckler
Super User
Super User

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

 sortby.png

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi ,

Capture.JPG

 

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

 

Thanks.

@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

@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!

The fact that we need a work around and Month is not simply a data type is incredibly frustrating. Power BI's capabilities dissapoint me everyday.  Things that seem like such an obvious need for handling data are missing or too finicky to be helpful.  The update was salt on the wound because WHY ON EARTH can't you change text size of values anymore.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.