Forum Discussion

rkh's avatar
rkh
New Member
6 years ago
Solved

Sorting by Month as Text (Jan Feb Mar etc)

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

Show and Tell
  • amitchandak's avatar
    amitchandak
    6 years ago

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

     

     

5 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    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-button

     

    Hopefully, this provides what you are looking for.

     

    Regards,
    Tom

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

     

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.