Problem with sorting month names in calender table

[FrankWe]

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

[PijushRoy]

Hi @FrankWe 

Please create a lookup table by enter data for MONTH SORT

Month	MonthSort
January	1
February	2
March	3
April	4
May	5
June	6
July	7
August	8
September	9
October	10
November	11
December	12

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]

Hi Pijush, 

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

Frank

[PijushRoy]

@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

[FrankWe]

And how will the month order look like when I have a visual that should show the last three months? I need to have the following order:      Nov - Dec- Jan 

If I use only the MonthNumber I think I will get:        Jan - Nov - Dec

🤔

Frank

[PijushRoy]

Hi @FrankWe 

The benefits of lookup table is if your fiscal month start from July and end in June, you should update only the SortOrder column in lookup table
Please see the example

Month	MonthSort
January	7
February	8
March	9
April	10
May	11
June	12
July	1
August	2
September	3
October	4
November	5
December	6

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

[FrankWe]

No, that has nothing to do with the fiscal year. The visuals must show a time period of the last three or six month as an example. The fiscal year is not changed, it is from January to December.

Frank

[PijushRoy]

Hi @FrankWe 

Please share sample pbix file link (allow access to all) and show expected result in screenshot
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[FrankWe]

Hello all,

sorry for not responding earlier. I still have the same problem. You can find an example file under the following link:

Power BI Example (Sort by month) 

In this exampe I would like to sort the table by the field "MonthYearNum" in the Calendar table but only show the name of the month in the table. In this case the sort order of the months should look like:

Okt

Nov

Dez

Jan

Feb

Mar

Apr

Mai

When you select the "Month" column in calendar table and then try to sort it by another column (in my case "MonthYearNum" this is not possible and an error message is shown. When I only have one year in the calender table (e.g. 2024) then this is working. 

So the only solution I have at the moment is to limit the content in calendar table to one year, then define the sorting of cloumn "month" by column "monthyearnum" and then fill the calendar table again with all needed data. this is wokring then, but that could not be the correct way to do that. There must be another way how to handle this.

Frank

[FrankWe]

No idea how to solve that problem?