Month Order in stacked column chart

[coolie]

There have been several supposed answers to this question, but often end up in a Desktop solution! I have produced the first image with year and month name columns (extracted from a run date field) in my table in the X axis, which is fine except the month order is wrong:

In the second image I have used the date field directly and used a custom format to extract the month name, but this is also wrong. How do I get the first image to sort correctly using bi report service? (ie not using bi desktop)

[v-echaithra · Accepted solution]

Hi @coolie ,

Thank you for reaching out to Microsoft Community.

Ensure your month name is sorted by a numeric month value and this needs to be present in your data model.
If your table already has something like:
MonthName = "January", "February", etc.
MonthNumber = 1, 2, 3, ..., 12

Now in the Data Modelling tab, click on the MonthName column
Choose Sort by Column > Month number
Refresh your visual, the months will now appear in the correct order.

To avoid repetition of month names as shown in the second chart, make sure your visual X-axis field is not too granular. Grouping by Year and Month number instead of full date helps.

Hope this will help.
Best Regards,
Chaithra E.

[coolie]

Managed to sort it using this link from MS but still took some working out. Downside with this is that you have to have all possible dates in the dimension table you create, so not really the best solution:

Solved: Re: Missing Date Hierachy Lakehouse Model - Microsoft Fabric Community

[coolie · Accepted solution]

Turns out much simpler than this - you create your date hierarchy in the semantic model in the way it describes in the link above, but as long as you have a date field in your table you dont need to a separate dimension table. You can create a view to add the other fields in the hierarchy if necessary.

[v-echaithra · Accepted solution]

Hi @coolie ,

Thank you for reaching out to Microsoft Community.

Ensure your month name is sorted by a numeric month value and this needs to be present in your data model.
If your table already has something like:
MonthName = "January", "February", etc.
MonthNumber = 1, 2, 3, ..., 12

Now in the Data Modelling tab, click on the MonthName column
Choose Sort by Column > Month number
Refresh your visual, the months will now appear in the correct order.

To avoid repetition of month names as shown in the second chart, make sure your visual X-axis field is not too granular. Grouping by Year and Month number instead of full date helps.

Hope this will help.
Best Regards,
Chaithra E.