Chaotic ordering of MonthYear in a chart when displaying last 12months values

[avelio]

Dears,

I'm facing a rather simple issue but i've gone at this every way that crossed my mind and haven't been able to fix it.

I'm using a line chart in order to display values chronologically. My date slicer is set to always display the last 12 months.

When doing this, the X axis is arranging my Month Year values wrongly and i don't know on which criteria should my 'Sort by column' be based on.

The only thing that comes to my mind is to have Month Number = 01,02,03,04,05,06,07,08,09,10,11,12 and when i create my MonthYear column it should be fine since it would be arranged from small to big as a value.

Any tips on how to display Month Number as '01,02,03...10,11,12' instead of '1,2,3....10,11,12'? 

[rajulshah]

Hello @avelio,

You can use column generated by the following formula for "Sort By Column":

YearMonthNo = FORMAT([Date],"yyyyMM")

Please let me know if this is not what you want.

Regards.

[avelio]

Hello,

Thanks for your reply.

Tried it, doesn't seem to work

Probably because 2017Apr is alphabetically lower than 2017Feb for example..

Print screen below:

[rajulshah]

Hello @avelio,

You will have the following values in the calculated column by the above mentioned DAX formula.

And you can then Sort the column which you are plotting in the visual by this calculated column using "Sort By Column" option.

Please revert if this doesn't work because it works perfectly fine in my report file.

[avelio]

Hello,

Highly appreciate your time helping me.

I have the above formula ( YearMonthNo = FORMAT([Date],"yyyyMMM") ) but for some reason it looks like this and i have no idea why.

I get the principle and for sure it would work if the formula would return what you showed in your prinscreen. It seems that "yyyyMMM" for me means 2017apr/2017aug and i have no idea why.

What's your input on this?

[rajulshah · Accepted solution]

Hello @avelio,

Please use YearMonthNo = FORMAT([Date],"yyyyMM") instead of YearMonthNo = FORMAT([Date],"yyyyMMM").

MMM means name of month with three initials.

Let me know if still it doesn't work.

[avelio]

You're absolutely right, i'm so dumb.

Thanks so much for your time. 

XO

[avelio]

Here's a picture of how my data is arranged atm. It shoul be Jan 2017, Feb 2017, March 2017.... Jan 2018 , Feb 2018