Sorting by Month as Text (Jan Feb Mar etc)

[rkh]

I used Format to get mmmyy from Date. But in reports, data is being sorted alpha. I want mmmyy sorted by month

[v-alq-msft]

Hi, @rkh 

You can also create two calculated columns as follows.

Month-Year = FORMAT('Table'[Date],"mmm-yy")
SortColumn = 
var _month = MONTH('Table'[Date])
var _year = YEAR('Table'[Date])
return
COUNTROWS(
        FILTER(
            ALL('Table'),
            MONTH('Table'[Date])>_month&&
            YEAR('Table'[Date])=_year
        )
)+1

Then you can make 'Month-Year' column selected, go to 'Modeling' ribbon, click 'Sort by Column', check 'SortColumn'.

Best Regards

Allan

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

[v-alq-msft]

Hi, @rkh 

Based on your description, I created data to reproduce your scenario. 

Table:

Calendar :

Calender = CALENDARAUTO()

There is a one-to-one relationship between them.

 

You may use Year and Month columns from Calendar table to display the result.

Best Regards

Allan

[amitchandak · Accepted solution]

You need to create a sort column and use that as sort column for month

Month sort = format(Date[Date],"MM")

Month Year sort = format(Date[Date],"YYYYMM")

[rkh]

Thanks Amit

[TomMartens]

Hey @rkh ,

basically, the default sorting of categorical columns like MonthName is alphabetical, if you want to change this behavior, it's necessary to create a column that contains the sortindex.

This is described in this article in general: https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column and especially: https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#sort-using-the-sort-by-column-butto...

Hopefully, this provides what you are looking for.

Regards,
Tom