Forum Discussion

ThomasSan's avatar
ThomasSan
Icon for Helper IV rankHelper IV
2 years ago
Solved

Sorting Month Axis by chronological order

Hi everyone,

 

I need help in ordering the y-axis in my visual by month in a chronological order

My order dates start in August 2023 and go until May 2024. Hence, I would like to have my months ordered in a chronolgoical order with August at the top and May at the bottom. As you can see in my screenshot, I have already drilled down from the year level to month level but, contrary to my expectations, the ordering of the months does not consider to which year each month belongs to. Does anyone therefore know what I can do to accomplish the desired result of having August at the top and May at the bottom?

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ThomasSan ,

     

    Thanks Joe_Barry  for the quick reply. I have some other thoughts to share:

    1.We can create a new table.

    Month Number
    2023-August 1
    2023-September 2
    2023-October 3
    2023-November 4
    2023-December 5
    2024-January 6
    2024-February 7
    2024-March 8
    2024-April 9
    2024-May 10

    2.We can create a [Year-Month] column in table.

     

    Year-Month = YEAR([Date]) & "-" & FORMAT([Date],"mmmm")

     

    3.We can create a model relationship.

    4.Select the [Month] column to sort by number column.

    5.Place the field as shown in the following image.

     

    Best Regards,

    Neeko Tang

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

6 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi ThomasSan 

     

    In Power query sort the order date from Earliest to Latest. Create an index column, load the table and then sort the order date by the index columns

     

    Joe

    • ThomasSan's avatar
      ThomasSan
      Icon for Helper IV rankHelper IV
      2 years ago

      Hi Joe_Barry 

       

      thank you for your reply. I tried the index column but I receive the follow message when I try to sort the order date column according to the index


      I assume this pops up because I several entries have the same date but a different index value. Do you happen to know how to deal with this?

      • Joe_Barry's avatar
        Joe_Barry
        Icon for Solution Sage rankSolution Sage
        2 years ago

        Hi ThomasSan 

         

        Ok delete the index and add this conditional column in Power Query  and Format the column into Whole Number

         

        Text.From(Date.Year([Date]) ) & Text.From(Date.Month([Date]))

         

        Sort by that column instead

         

        But I'm thinking you may need to go down the Year/Month route in your visual.

         

        Still do the above and in theTable view add a column then sort by the column above. You can adapt the formatting to suit your needs. I think without the context of the year, it won't be possible to achieve what you need, but I stand to be corrected

        Year/Month = FORMAT(Incidents[DateUTC], "YY - MMM")

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ThomasSan ,

     

    Thanks Joe_Barry  for the quick reply. I have some other thoughts to share:

    1.We can create a new table.

    Month Number
    2023-August 1
    2023-September 2
    2023-October 3
    2023-November 4
    2023-December 5
    2024-January 6
    2024-February 7
    2024-March 8
    2024-April 9
    2024-May 10

    2.We can create a [Year-Month] column in table.

     

    Year-Month = YEAR([Date]) & "-" & FORMAT([Date],"mmmm")

     

    3.We can create a model relationship.

    4.Select the [Month] column to sort by number column.

    5.Place the field as shown in the following image.

     

    Best Regards,

    Neeko Tang

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

    • ThomasSan's avatar
      ThomasSan
      Icon for Helper IV rankHelper IV
      2 years ago

      hi Anonymous 

      thank you for your response! this was the solution! 🙂