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 data starting in September 2024 to April 2025, then I would have issue displaying the Month, as it starts in January. How should I take care of this? Thanks.

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 🙂

3 Replies

  • Aala_Ali's avatar
    Aala_Ali
    Icon for Most Valuable Professional rankMost Valuable Professional
    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 🙂

  • burakkaragoz's avatar
    burakkaragoz
    Icon for Super User rankSuper User
    1 year ago

    Hi tan_thiamhuat ,

     

    Yeah this is a common issue when your data doesn't start from January. What you can do is create a custom sort order based on your actual starting month.

    Here’s a quick way to do it:

    1. Add a calculated column in your Date or Month table:
    CustomMonthOrder = 
VAR StartMonth = 9 -- change this to your actual starting month
RETURN MOD([MonthNumber] - StartMonth + 12, 12) + 1
    1. Then go to the Modeling tab, select your Month column, and click Sort by Column → choose CustomMonthOrder.

    This way, your months will show up in the right order based on your dataset, not just the calendar year.

    Hope that helps!

     

    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.