Forum Discussion

tan_thiamhuat's avatar
tan_thiamhuat
Icon for Post Patron rankPost Patron
1 year ago
Solved

Power BI: Month - Sort By Month Number

I have this Month, which is Alphabetical, and sorted by the MonthNumber (1,2,3,...9,10,11,12) which is OK when we have whole set of data for the whole year starting in January. But if I have a set of...
workspaces
  • Aala_Ali's avatar
    Aala_Ali
    1 year ago

    Hi tan_thiamhuat 


    Create a Custom Sort Order Based on Your Dataset's Starting Month

    You need a custom sort column that aligns with your actual data range — in this case, starting from September.

    🔧Steps to Fix It:

    1. Create a calculated column (in your Date table or your Month table):

      DAX
       
      CustomMonthOrder =
      VAR StartMonth = 9 -- September (adjust as needed)
      RETURN
      MOD([MonthNumber] - StartMonth + 12, 12) + 1

      ✅This will create an order where:

      • September = 1

      • October = 2

      • ...

      • August = 12

    2. Sort your Month column by CustomMonthOrder:

      • Go to the Modeling tab

      • Select your Month column

      • Click "Sort by Column" → choose CustomMonthOrder

    3. Now, your visuals will show months in the correct order based on your dataset’s starting month, regardless of the calendar year.

      If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful 🙂

v-sgandrathi's avatar
v-sgandrathi
Icon for Community Support rankCommunity Support
1 year ago

Hi tan_thiamhuat,

 

When you use Month sorted by Monthnumber (1–12), Power BI will always sort starting from January, regardless of your actual data's range (e.g., starting in September 2024 and spanning into April 2025).

To fix this and customize the month sorting dynamically based on your data's starting point (e.g., September), follow this approach:

Create a Date Table:
In Modeling go to Nwe Table 

MonthOrder =
DATATABLE (
    "Month", STRING,
    "SortOrder", INTEGER,
    {
        {"September", 1},
        {"October", 2},
        {"November", 3},
        {"December", 4},
        {"January", 5},
        {"February", 6},
        {"March", 7},
        {"April", 8}
    }
)

 

Create a relationship between your data (month) and month order (month) and then To apply the custom sorting, click on the MonthOrder table and select the Month column. Then, go to the Column tools tab in the ribbon and click on Sort by Column, choosing SortOrder from the list. This step ensures that your months appear in the desired order—starting with September, followed by October, November, and so on—based on the custom logic you defined in the SortOrder column.

 

I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

 

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

 

Thank you.

Sahasra 

Community Support Team.