Forum Discussion

mkane12's avatar
mkane12
Helper I
1 year ago
Solved

sorting months in order

how can I sort my months in order?   
Need Help
  • v-tejrama's avatar
    v-tejrama
    1 year ago

    Hi mkane12 ,

     

    Thank you for your follow-up, and you’re correct to highlight that. If you’re already using a date table, such as a Calendar table with all dates included, there’s no need to create a separate Month table just for sorting. You can sort the month names directly in your existing date table.

    Make sure your table has a column for the month name (like "January", "February") and another for the month number (1 to 12).

     

    Thank you.

     

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

Hi mkane12 ,

 

Thanks for your question! Sorting months in the correct chronological order is a common challenge in Power BI, especially when months are stored as text.

Here's how to fix it:

  1. Create a Month Number Column\ If your month names are in a column (e.g., "January", "February", etc.), create a new column that assigns a number to each month:

    MonthNumber = 
SWITCH(
    TRUE(),
    'YourTable'[Month] = "January", 1,
    'YourTable'[Month] = "February", 2,
    'YourTable'[Month] = "March", 3,
    'YourTable'[Month] = "April", 4,
    'YourTable'[Month] = "May", 5,
    'YourTable'[Month] = "June", 6,
    'YourTable'[Month] = "July", 7,
    'YourTable'[Month] = "August", 8,
    'YourTable'[Month] = "September", 9,
    'YourTable'[Month] = "October", 10,
    'YourTable'[Month] = "November", 11,
    'YourTable'[Month] = "December", 12
)

  2. Sort by Month Number

    • Go to the Data view.
    • Select the column with month names.
    • In the ribbon, click Sort by Column and choose the MonthNumber column.

This will ensure your months appear in the correct order in visuals and tables.

For more on sorting in Power BI, check out:
https://learn.microsoft.com/power-bi/create-reports/desktop-sort-by-column

Let me know if your months are part of a date field or if you need help adapting this to your model!

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.

This response was assisted by AI for translation and formatting purposes.

  • mkane12's avatar
    mkane12
    Helper I
    1 year ago

    hi, my months are part of a date field, I do not have a column with just Months

    • MasonMA's avatar
      MasonMA
      Super User
      1 year ago

      Hi mkane12 ,

      This wouldn't be a good long-term strategy, but for now if you just wanted to sort them with a simple way, you'd still need to extract your MonthNumber from your date by creating one new calculated column. (To use below DAX, You date should be in 'Date' data type, not 'Text' data type)

      MonthNumber = MONTH('Table'[Date])

      then Select MonthName column by going to Column tools, Sort by column 'MonthNumber'.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      1 year ago

      Hi,

      You should create a Calendar table with calculated column formulas for Month name, Month order and Year.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of your Fact table to the Date column of your Calendar table.  To your visual, drag Month name from the Calendar table.  Create a ytear slicer from the Calendar table and select a year.