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
But Instead of it. its ordering the months based on alphabetical order as
So, Could anyone please help me to achieve the above stated order based on months.
Solved! Go to Solution.
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")
You can with another table with only Year and Number column. Get the value like below
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.
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.
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
But coming to report its showing the order as per alphabets.
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.
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.
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.
From here could you please guide me to next steps which I have to do.
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.
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
Is there anything I missed.
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.
I have created the date column and month number and sorted it according to month number.
I have the output nearby I expected
But the exact output has to be like yours.
How can I achieve it.