Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yiruan-msft
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
v-yiruan-msft
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.
jdbuchanan71
Super User
Super User

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.