Forum Discussion
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
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! :smileyhappy:
5 Replies
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! :smileyhappy:
It worked.
Thank you.
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!
Thanks!
