Matrix Sort Order by Month and Year Problem

[Anonymous]

I have a date table with added columns, created a relationship, date slicer and matrix visual and cannot seem to find the  reason the sort wont work correctly. December should not be first. I would like to sort by month/year with the oldest first. I also have the date table marked as date. What am I missing?

Thank you,

[Anonymous]

@Anonymous,

you can sort Month by Date instead. Click on your Month in the Fields pane --> Modeling Ribbon --> Sort by Column and select Date

[Anonymous]

NickNg278 - If I understand correctly I have already done that.

[Anonymous]

@Anonymous,

select Date instead of Month (Default)

[Anonymous]

My apologies NickNg278 - I was in the process of rereading and doing what you had correctly written before and found my mistake. Having done that I get this error.

[Anonymous]

@Anonymous,

No problem. You can create another column in your date table using this format "YYYY/DD" (YearMonthnumber) then sort your Month by YearMonthNumber

[Anonymous]

Still no go. I'm thinking I have a problem to fix in the one to many relationship. I can't sort any column with multiple values by a column that has unique values without getting the same error for different columns.

[Mond]

The data type is "Text" on Month column.

Please change it to Date format and it should work.

[Anonymous]

Mond - Changing data type only caused errors.

[Mond]

Hi Hanley,

I got a question, when your Date coulmn show value (1/1/2000), how come Month value shows "Jan05".

Can you please duplicate Date column and use Data Type = Date, and Format (MMMM yyyy)

[Anonymous]

Great catch Mond - I had not added the table/column name correctly. I had only DATE when it should have been DateTBL[Date]. That gives me the correct MonthYear (Jan00) but I still cannot change format from Text to Date or sort column by Date column with out the errors. Nor fix my original problem of sorting in the correct order.

[Anonymous · Accepted solution]

Mond - Your suggestion got me in the ball park for the next step. I'm sure you would have done something similar but I found an answer here: https://community.powerbi.com/t5/Desktop/how-to-sort-Date-MonthYear-column/td-p/102700

I did what Tri Nguyen suggested by adding another numerical column which I could sort my MonthYr column by.  It is multi to multi relation but not text. Worked perfectly. Thank you both!

[Anonymous]

bump