Sort Date hierarchy with year

[mmoroni]

I have a visual that takes the date hierarchy of month; however it places December 2020 to the far right.  I am trying to have the columns go from Oldest to newest, or vice versa if needed.  But as it is, it is months according to a year.  I want YEAR, then month.
Dec 2020, Jan 2021, Feb 2021, etc.

Any suggestions? 

[v-yiruan-msft · Accepted solution]

Hi @mmoroni ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. If no, please provide some sample data with Text format and expected result with examples. Thank you.

1. Create a custom column to get YearMonth in Power Query Editor

2. Create another month name to replace the month of date hierarchy

Month = FORMAT('Table'[Date],"mmmm")

Best Regards

[jdbuchanan71]

@mmoroni 

Add a column to your Dates table like this.

Month Year Date = EOMONTH ( Dates[Date], 0 )

This will give us the last date of each month. 

Then go to the model view and select that column to apply custom formatting and put in the format 'mmm yyyy'

Then use the Dates[Month Year Date] column in your visual.  It is formatted to show the correct string but also sorts in the correct order automatically.

[mmoroni]

Thank you for your suggestion.  However, it did not resolve my issue.  I need column headers for each mmmm yyyy to be in proper order, and this did not resolve that 😞

[jdbuchanan71]

Please share your .pbix file.  You can post it to DropBox or OneDrive and share the link here.  If implement correctly my suggestion will do what you are wanting.