Forum Discussion

mmoroni's avatar
mmoroni
Helper I
5 years ago
Solved

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 i...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
jdbuchanan71
Super User
5 years ago

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's avatar
mmoroni
Helper I
5 years ago

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's avatar
    jdbuchanan71
    Super User
    5 years ago

    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.