Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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.
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
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.
Thanks,
Joshua
Hi
I understand your issue.
You need to add the month number as sort column for your column (MMM-YYY).
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.
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.
From here could you please guide me to next steps which I have to do.
Thanks,
Joshua
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.
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
Is there anything I missed.
Thanks,
joshua
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.
I have the output nearby I expected
But the exact output has to be like yours.
How can I achieve it.
Thanks,
Joshua
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
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?
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |