Date Format

[revansh]

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

[Sean · Accepted solution]

@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

That should do it.

Good Luck!

[jhauff_RFO]

Thanks!

[Anonymous]

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!

[Anonymous]

select the column and go to column tools.

Select the data type and set it as "Date"

select format and type as mmm-yy

[revansh]

It worked.

Thank you.