Month and Year field not in order

[gbarr12345]

Hi,

I created a calculated column to combine the month and year as below:

However, when I pull it in to a graph, I want it to be in order. For example, like January 2024, February 2024, March 2024 etc.

It is instead putting all the January's together and so on:

Is there an easy way to fix this?

Many Thanks in advance.

[Jihwan_Kim]

Hi,

One of many ways is, I suggest creating one more calculated column, and that is Month Year sort Calculated Column. After creating it, configure Month Year column to be sorted by Month Year sort column.

Please check the below picture and the attached pbix file.

Month Year sort CC = 
EOMONTH('calendar'[date],0)

EOMONTH function (DAX) - DAX | Microsoft Learn

[gbarr12345]

Hi Jihwan,

Thank you for your reply.

I tried all your steps, however it's still not in the order I'm looking for. See screenshots below:

[Ritaf1983 · Accepted solution]

Hi @gbarr12345 
Since month year is a text data type, the engine cannot sort it chronologically, because it is just a string.

So you should add a numeric column that will hold the sorting logic.

This column can be a combination of year and month, you can add it from power query to your calendar table :

and then from the data view sort your column :

More information about working with calendar tables include a full script for creating it here :

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Information about sorting by another column :

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto...

PBIX with my example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[gbarr12345]

Hi Rita,

Thank you for your response.

I can't seem to see the option to create a custom column.

Is it because we may have different versions of power bi or something?

[Ritaf1983]

No, this is because you are not in POWER QUERY.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[gbarr12345]

Thanks for the help Rita!

[Ritaf1983]

Happy to help 🙂

[gbarr12345]

Hi Rita,

I tried creating the custom column there and it seems to be throwing out an error as below.

Any idea what's wrong with it?

[gbarr12345]

I actually I fixed the above, it was just a syntax error.

However, I'm now getting this error on the last step you showed through the pictures above:

[Ritaf1983]

Hi @gbarr12345 

This error message indicates an inconsistency in your Month and Year columns that conflicts with the sorting criteria.

Explanation:

The error message arises when the sorting operation encounters duplicate values in the column being sorted (Month and Year) while attempting to sort by another column (presumably, a column containing unique values). This inconsistency prevents Power BI from determining the correct order for the elements.

Troubleshooting Steps:

1. Check for Empty Cells: Ensure that there are no empty cells in the Month and Year columns. Empty cells can be interpreted as different values, leading to sorting inconsistencies.

2. Verify that you use a calendar table like in the pbix, attached in my first response.

3. Data Type Validation: Validate that the data type of the sort column is set to "Wole Number" and not "Text". Treating these columns as text can lead to misinterpretations during sorting.

For more assistance, I need to see your pbix.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[gbarr12345]

Thanks for the explanation Rita, I'll follow those steps!

Many Thanks for your help!

[gbarr12345]

Apologies, I found it. Ignore the above thanks!