Monthname not sorting in unpivot columns

[Anonymous]

Hi,
I have a matrix in power bi where I want to show from the slicer if Year is selected only year should show, if Quarter then Quarter should show on the matrix and when month is selected MonthName should show. I have created a table with unpivot columns and the monthname is not sorting. Everything else works except the sorting. Please help. Thanks Dorathy

[amitchandak]

If your month is in MMM-YYYY or MMMM-YYYY format then you need a month year sort column

New column

Month Sort =format(date[date],"YYYYMM")

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Anonymous]

Slicer          

Day

Year 

Quarter

MonthName

Week

Day

If Monthname is selected the Matrix shows as

Department Apr 20 Feb 20 Mar 20 Jan 20

[parry2k]

@Anonymous add another column for sorting month-year column and sort column

Month Year Sort = 
FORMAT( Table[Date], "YYYYMM" )

sort your month year column with above new column called Month Year Sort 

[Anonymous]

Thanks for your reply, you want me to add this to the already unpivot columns table as a new column?

[Anonymous]

Values in the unpivot columns are not sorting by Month

[parry2k]

@Anonymous doesn't matter, just follow the step, add a new sort column and sort your month name column by this new sort column as described above and it should be ok. You have to first test it and let us know if you run into an issue. It is not clear if you are actually trying the solution.

[Anonymous]

@parry2k  - This is the error I am getting 

Column 'Year Month' in table 'DateSlicer' cannot be found or may not be used in this expression.

I have this Year Month in the unpivot column along with other columns like Year, Quarter, Year Month, Week and Day

[parry2k]

@Anonymous can you share pbix file, please remove any sensitive information before sharing. Unfortunately, I'm having a hard time following what issue you are facing. If you cannot share publically, my email address is in the signature and feel free to send it by email.

[parry2k]

@Anonymous yes whatever table you are using for month year column.

[parry2k]

@Anonymous you have to sort month name by month number, assuming you have month like Jan, Feb, Mar and month number 1, 2, 3,...

Select month name column and on column tools choose sort by , see example below, it is not exactly the same column but shows where the option are to sort by

[Greg_Deckler]

Sample data would help. But you likely need a numeric month column and use Sort By column on you monthname. Otherwise it will sort alphabetically.