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
Joshua
Frequent Visitor

How to order mm-yy format based on months and not in alphabetical order

Hi,

 

I am new to Power BI.

 

I just like to know , how to order the 'mm-yy' format based on month order and not in alphabetical order.

 

My scenario is, I am picking months for year 2002 -2005 . I like to display the data as

 

(Jan-05

Feb-05

to

Dec-05

 

Jan-04

to

Dec 04

 

Jan-03

to 

Dec-03

 

Jan-02

to

Dec-02)

 

But Instead of it. its ordering the months based on alphabetical order as

 

(Apr-02

Apr-03

to

Apr-05)

 

 

So, Could anyone please help me to achieve the above stated order based on months.

 

Thanks,

 

Joshua

 

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Yes. But better you can use the below formula for the new column. It will more effective.

 

The format is 200501, 200502 etc. This will be the best for sorting the column.

 

YearMonth = FORMAT('MONTH'[DATE_COLUMN],"yyyymm")

View solution in original post

Anonymous
Not applicable

You can with another table with only Year and Number column. Get the value like below

 

Year   No

2008   1

2007    2

2006    3

2005    4

 

Join the latest table with your existing table with Year column. Now you can get the No column in your existing table and you can concatenate function CONCATENATE(column1,column2). Here column1 is latest "No" column and column2 is Yearmonth column. 

 

You will get value like 1200801, 1200802,.......4200501, 4200502,............. Now you can use this column for sorting.

 

Thanks

Hari

View solution in original post

15 REPLIES 15
Anonymous
Not applicable

Hi

 

  To get the format as like Jan-05, use the below formula.

 

Monthyear = FORMAT('Table'[Date],"MMM-yy")

 

If you have MonthNumber column then you can sort that column to get proper order. If you don't have month number column then get using below formula.

 

MonthNo = MONTH('Table'[Date])

 

Note: Replace Table with your table name and Date with your date column.

 

 

order.png

 

Thanks

Hari

Hi Hari,

 

I tried those steps before, but couldn't able to get a solution.

 

I used Oracle sql and displayed the data as below. In data model its showing fine as I expect

 

Data model.GIF

 

But coming to report its showing the order as per alphabets.

 

Report view.GIF

 

 

So, here where I got stucked up with.

 

I want those months to be displayed as a checkbox.

 

Any other tricks to solve it.

 

Kindly please let me know.

 

 

Thanks,

 

Joshua

Anonymous
Not applicable

Hi

 

 I understand your issue. 

 

 You need to add the month number as sort column for your column (MMM-YYY).

 

Sort_Slicer.pngSort.png

Hi @Joshua,

 

The column will order by date if we change the column data type to date. As the image below, change the colomn data type to "Date", then choose a favorite format which isn't exactly the same with yours. But it works. Hope this would help.How to order mm-yy format based on months and not in alphabetical order.jpgHow to order mm-yy format based on months and not in alphabetical order2.jpg

 

 

 

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous,

 

 Yeah that's what I want

 

Could you please explain this step by step

 

The below is my query section.

 

Here the data model which contains period_month is in text data type. 

 

Edit Querries section.GIF

 

 

From here could you please guide me to next steps which I have to do.

 

 

 

Thanks,

Joshua

 

 

Anonymous
Not applicable

Hi

 

Follow the below steps.

 

Step 1: Click Data Tab as shown below

 

Step 2: Choose the Column (PERIOD_MONTH) and select Modeling tab on top.

 

Step 3:  Choose the Sort option and select the month number, if there is no monthnumber column then create a new column with Month(date) function.

Steps.png

 

Hi @Anonymous,

 

I completed the steps which you adviced,

 

And while sorting I am facing below error. Stating that its already sorted. On displaying in report its not in proper order Sorting.GIF

 

Is there anything I missed.

 

Thanks,

joshua

Anonymous
Not applicable

Hi

 

 I am not sure how you generated MonthNumber column. I couldn't see any date column in your table. Try to get monthnumber from date column and hope everything will work as i showed above.

 

Thanks

Hari

Hi @Anonymous,

 

I have created the date column and month number and sorted it according to month number.

 

Date Column.GIF

 

I have the output nearby I expected

 

Date output.GIF

 

But the exact output has to be like yours.

 

Date output1.GIF

 

How can I achieve it.

 

Thanks,

 

Joshua

 

 

Anonymous
Not applicable

Hi

 

  I am happy that you achieved the result. 

 

 I hope that you are expecting Year wise sort order. If that is the case, then create an another column with the format (year-month) as 2005-01, 2005-02, etc like that and that column for sorting instead of just month number column.

 

 Now monthnumber column is from 1 to 12 so it is sorted like first month of all the year and so on. 

Yeah thats what Iam expecting a year wise sort.

 

I got your point , but I need some more clarity in creating another column as (Year-month) format.

 

so this is what you want me to do

 

Year = FORMAT('MONTH'[DATE_COLUMN],"yy-mm")

 

Or something else needed

 

 

Anonymous
Not applicable

Yes. But better you can use the below formula for the new column. It will more effective.

 

The format is 200501, 200502 etc. This will be the best for sorting the column.

 

YearMonth = FORMAT('MONTH'[DATE_COLUMN],"yyyymm")

Yeah its displaying well 

 

But it should display the maximum year first such as 2005 - 2002

 

But its in Ascending order.

 

Any solution for it?

 

 

Anonymous
Not applicable

You can with another table with only Year and Number column. Get the value like below

 

Year   No

2008   1

2007    2

2006    3

2005    4

 

Join the latest table with your existing table with Year column. Now you can get the No column in your existing table and you can concatenate function CONCATENATE(column1,column2). Here column1 is latest "No" column and column2 is Yearmonth column. 

 

You will get value like 1200801, 1200802,.......4200501, 4200502,............. Now you can use this column for sorting.

 

Thanks

Hari

Hi Hari,

 

Bravo!!!

 

Thanks a lot for your advice.

 

Finally got the expected output.

 

Really great.

 

 

Regards,

Joshua

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.