Power BI Desktop - How to sort alpha months across 2 years?

[Dimani]

Hi Power BI Desktop Community,

In Power BI Desktop, how to order months, when the months are spelled out and when the months are across 2 different years, for example, July 2023 to Jun 2024?

I tried using an index column with July being 1, August being 2 ... and sorting the index column, but that did not work.  I used column tools - sort by column.

I changed the index columns and/or the data columns to be text fields, but it did not work either. I ended up preceding the months with 01, 02, ....

Though it works, I wonder if there is a better way to do this?

Thanks

[lbendlin]

You describe a fiscal calendar.  While you could write DAX or Power Query code, this would be a waste of your time.  Calendars are immutable (their data never changes) so there is no need to compute them in Power BI. 

Prepare a reference table (for eaxmple an Excel file on a Sharepoint) that has all the required columns including the sort columns, fiscal year months etc. Then include that reference table in your data model.

[OwenAuger · Accepted solution]

Hi @Dimani 

Here are a couple of options.

I'll refer to your existing column as "Month Year Text".

1. To avoid having to set a Sort By Column:

• Change "Month Year Text" to a column of type date (or add such a column)
• Set its value to the first day of the month (for example)
• Set is format string to "MMMM yyyy" or similar.

2. If you would like to keep the "Month Year Text" column and apply a Sort By Column:

• Add a column "Start of Month" of type date containing the first of the month
• Set "Month Year Text" to Sort By "Start of Month".

Examples attached.

Also see this article:

https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/

Regards

[Dimani]

Hi OwenAuger and Ibendlin,

Thanks for both of your reply. After reading it, I went back to take a look at it again. I did not mention this in my original post; my data source is Excel.

Turns out using an index column works. I do not remember the exact sequence of events. But I did something like these in Power BI: changed the index column from text to whole number and selected don't summary.  Afterwards, sorting can be based on the index column, and that's the order we want.

Best regards.