Forum Discussion

FrankWe's avatar
FrankWe
Helper II
2 years ago

Problem with sorting month names in calender table

Hello all,

 

I have a problem with sorting in calender table. I have a visual that shows the name of the month on the X-axis. To sort this correctly I selected the column "Month" and let this column then be sorted by column "MonthYearNum". The problem is now that this is working when there is only one year in the calender table (e.g. 2023). But when an additional year is in the calender table (e.g. 2023 and 2024) it is no longer possible to sort the "Month" column by "MonthYearNum" column. I get an error message telling me that I have to sort by another column as it is not possible to have more than one value in "MonthYearNum" for the same value in "Month".  You can see the structure of my calender table below and the also the error message (in German). 

 

I am more or less sure that this was working some months ago with an older version of Power BI Desktop. What also is strang is that when I only have one year in the calendar then it is possible to sort the month name by "MonthYearNun". When this has been setup and after that I add an additional year to the calendar table the sorting is still working.  But when I then switch the sorting to another column and then try to switch back to "MonthYearNum" I get the error message again. 

 

So my question is now what is going wrong and how can I use the calendar table to show the name of the month on the X-Axis  but sorting them correctly by using the column "MonthYearNum" for it? 

 

 

 

 

 

Regards

Frank

 

Need Help

9 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    Hi FrankWe 

    Please create a lookup table by enter data for MONTH SORT

    MonthMonthSort
    January1
    February2
    March3
    April4
    May5
    June6
    July7
    August8
    September9
    October10
    November11
    December12


    In this table month should be Sort By "MonthSort" column

    Create a relationship with your fact table with "Month" column
    Use "Month" from Lookup table in visual

    Let me know if that works for you


    If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

    Thanks
    Pijush
    Linkedin

    • FrankWe's avatar
      FrankWe
      Helper II
      2 years ago

      Hi Pijush, 

       

      I have the same info (MonthNumber) in my calender table, too. But I will try and let you know. 

       

      Frank

      • PijushRoy's avatar
        PijushRoy
        Community Champion
        2 years ago

        FrankWe 

        In Calendar table, if you can use the MonthOrder as SortBy it will work but 
        Separate lookup table with sort by helps.

        Please check and let me know

        If your requirement is solved, please make CORRECT ANSWER a SOLUTION