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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 

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

 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors