cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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

 

Capture.JPG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

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

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.

 

 

View solution in original post

4 REPLIES 4
Highlighted
Resolver I
Resolver I

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

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.
Highlighted
Anonymous
Not applicable

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

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

 

c1.JPG

Highlighted
Resolver I
Resolver I

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

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.

 

 

View solution in original post

Highlighted
Super User IV
Super User IV

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

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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors