How to convert Date format in "MMM-YY" but order sequence become problem

[Anonymous]

Hi,

I have a question in which I want my date format in my visualisation to "MMM-YY", I know I can make use of the following DAX to perform and get the format I want:

=FORMAT(Data[Date],"MMM-YY")

However, the above format will convert the Date into a Text. This become a problem in which my Visualisation will not show in the sequence of the monthly order. For example, you notice from the following graph in which the order run out because date become a text format.

Any idea? thanks..Tuff

[Anonymous]

Hi @Anonymous ,

Create another column like

YearMonth = (YEAR(Data[Date])*100)+MONTH(Data[Date])

Use this column for sorting order of formated date column.

I hope this will solve your problem.

[Anonymous]

Hi ORBI,

I had created a calculated column with the text format (YearMonthShort) I want and this column refer to another column (Reporting Month) in my query.

Hence, how do you sort and how do you sort in the visualisation? Thanks

[amitchandak]

In DataView. Once click on any column. You will sort on column View. Click on this column and choose the sort column.

There best sort column for month year is YYYYMM. Create it using format.

[Anonymous · Accepted solution]

Select a column in Fields. Then go to Modelling tab. Go to Sort by column and select the desired column to be used in sorting selected column.