cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper II

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

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'?

1 ACCEPTED SOLUTION
Super User

Hello @avelio,

MMM means name of month with three initials.

Let me know if still it doesn't work.

7 REPLIES 7
Super User

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.

Helper II

Hello,

Tried it, doesn't seem to work

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

Print screen below:

Super User

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.

Helper II

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.

Super User

Hello @avelio,

MMM means name of month with three initials.

Let me know if still it doesn't work.

Helper II

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

Thanks so much for your time.

XO

Helper II

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