cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joshua Frequent Visitor
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

Accepted Solutions

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

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")

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

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

15 REPLIES 15

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

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

Joshua Frequent Visitor
Frequent Visitor

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

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

Community Support Team
Community Support Team

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

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.

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

Hi

 

 I understand your issue. 

 

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

 

Sort_Slicer.pngSort.png

Joshua Frequent Visitor
Frequent Visitor

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

Hi @rhariharaneee,

 

 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

 

 

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

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

 

Joshua Frequent Visitor
Frequent Visitor

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

Hi @rhariharaneee,

 

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

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

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

Joshua Frequent Visitor
Frequent Visitor

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

Hi @rhariharaneee,

 

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