Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
FrankWe
Helper I
Helper I

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? 

 

FrankWe_0-1707205032359.png

 

 

FrankWe_1-1707205032361.png

 

 

Regards

Frank

 

7 REPLIES 7
PijushRoy
Super User
Super User

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

Hi Pijush, 

 

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

 

Frank

@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

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

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

MonthMonthSort
January7
February8
March9
April10
May11
June12
July1
August2
September3
October4
November5
December6

 

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

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

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...

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.