Chart months - out of order

[bryanc78]

Data/column format - 1/2/2019

Axis format - Month hierarchy

chart sort - Month, ascending order

Trying to get January to show on the right side and it's not working, even though the other months are.  Is there a simple setting I'm missing?

[Greg_Deckler]

Strange, usually this is controlled by a numeric "Sort By" column. Do you have of those?

[bryanc78]

No I do not.

I'm thinking I need to duplicate my date column then transform that to monthnumber?  So 1/2/2019 will now be 1?

Then I can sort that way

No other way?

Edit- that works but now January is at the beginning as it comes before October; 1 vs 10.  Need to figure out how to add the year in there

[sureshsonti]

Yes you can create a duplicate column of your date column and transform that to month. Then you can sort it my month number. I do it all the time. Sort should have Ascending and Descending. You can dupliate the date column and transform it to year.

[bryanc78]

@sureshsonti

Can you expand on that a bit more?  I tried this solution Here but now I'm getting a can't sort by another column error message

[Greg_Deckler]

@bryanc78 - So, assuming you have a date column, then you should duplicate that column in the Query Editor, let's call it [Date 2], then you should be able to create something like this:

Column = YEAR([Date 2]) & FORMAT(MONTH([Date 2]),"0#")

And use that as your sort by, you should end up with text strings like:  201801, 201802...201901, etc.

[bryanc78]

@Greg_Deckler

It's not working.  Here is what I have:

Original column - Call Date

I duplicated that column and called it Call Date 2

I then created a calculated column using your formula and got the results I was looking for; 201901, 201811 etc.  This column is called Call Date - MonthYear

In my visualization, I put Call Date 2 into the Axis, and removed all levels but Month.  The issue is when I try to sort Call Date 2 by Call Date - MonthYear I get the "sort by another column" error

What's perplexing is it's working for October - December, sorted by Month ascending but January is throwing it off

[Greg_Deckler]

And you duplicated Call Date in Power Query and not DAX?

[bryanc78]

@Greg_Deckler

Yes, duplicated the column in Power Query and in normal viewing, outside of Power Query, I created a new column and formatted it based on your instructions

[Greg_Deckler]

I'm not sure what is going wrong here perhaps I am missing some component. See Table 11 of the attached PBIX. I started with an Enter Date query with just Date column. In Power Query, I created a duplicate column, Date - Copy. I created a sort Column based on Date - Copy. I can sort Date based upon Sort.

[bryanc78 · Accepted solution]

Looks like I got it to work

I duplicated call date and transformed it to Name of Month.  I put that into the axis and was then able to sort that by Call Date - Month Year

Not sure why just a straight duplicate would not work until I transformed it

[sureshsonti]

Createa  calculated column for MonthName

MonthName = FORMAT(Sheet1[Date],"MMM")

Use MonthName and Day on the Axis

On the visualization you will  see an UP ARROW, DOWN ARROW and 2 DOWN ARROWS and another symbol which is also 2 DOWN ARROWS. Select the 4 one and you should get the screenshot that I attached