cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmoroni
Helper I
Helper I

Sort Date hierarchy with year

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? 

 

mmoroni_0-1617892783974.png

 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

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

yingyinr_0-1618302720593.png

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

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

yingyinr_1-1618302881308.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
yingyinr
Community Support
Community Support

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

yingyinr_0-1618302720593.png

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

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

yingyinr_1-1618302881308.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

jdbuchanan71
Super User II
Super User II

@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. 

jdbuchanan71_0-1617893289155.png

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

jdbuchanan71_1-1617893436672.png

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.

jdbuchanan71_2-1617893536247.png

 

 

 

 

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 😞

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors