cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
revansh Member
Member

Date Format

Hi Everyone,

 

               I have a line graph with month start date on X-axis. 

 

Month start date format = MM-YYYY (i.e. JAN2017 , FEB2017, MAR2017).

 

I want to change the format to MM-YY (ie JAN17, FEB17,MAR17 etc..) which is not available in the data formats list.

 

how can i get this format with correct sorting.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Date Format

@revansh

 

Create 2 columns (1 to display and to sort it)

 

Custom Column = FORMAT('Calendar'[Date], "MMM YY")

Custom Column Sort Order = 
INT (
    CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) )
)

Then Select the Custom Column => Modeling Tab => Sort Column By => select the Custom Column Sort Order column

 

Sort Column By.png

 

That should do it.

Good Luck! Smiley Happy

3 REPLIES 3
Super User
Super User

Re: Date Format

@revansh

 

Create 2 columns (1 to display and to sort it)

 

Custom Column = FORMAT('Calendar'[Date], "MMM YY")

Custom Column Sort Order = 
INT (
    CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) )
)

Then Select the Custom Column => Modeling Tab => Sort Column By => select the Custom Column Sort Order column

 

Sort Column By.png

 

That should do it.

Good Luck! Smiley Happy

revansh Member
Member

Re: Date Format

It worked.

 

Thank you.

Highlighted
arunmadanan Visitor
Visitor

Re: Date Format

Thank you Sean. That worked for me also. Any idea how we can do it for a YTD chart?

 

I am using a measure that use monthly sales cost and date to get YTD for each month. Something like this.

 

YTD = TOTALYTD(SUM(Sales[Cost]),Sales[Date]) 

 

But when I use, Date(MMM YYYY) = FORMAT(Sales[Date],"MMM YYYY") in the charts, it messes up my YTD chart.

 

Any ideas how to can we make it work?

 

Thanks!