Custom date format

[JennaExe]

Hi, I want to shorten the dates on my graphs from e.g. "September 2023" to "Sep-23". I found a way to do this online, by creating a new column in the modelling tab and using the formula Short_Date = FORMAT (‘Table name'[Date], “MMM-YY”). It converts the dates to the right format, but it creates it as Text, which means when I use it on the graph, it sorts the axis alphabetically rather than in date order. I tried converting the type to Date, but that just creates an error where all the dates just show on the Reports tab as #ERROR. Does anyone know how to fix this, so that it is recognised as a Date field and displays in this format? Thank you!

[manvishah17 · Accepted solution]

Hi @JennaExe , 
You have created MonthYear column. 
Now create a new column by this formula 

Sortkey = FORMAT('Date'[Date], "yymm")

Then sort your month year column by this SortKey column.
Sorting

Output

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[JennaExe]

This has solved it, thanks so much for your help! And thanks to the others for trying to help too!

[Idrissshatila]

it works from my side, there's something missing at your end

[cherrybi_com]

Hi

Create another calculated column 

Month = MONTH(‘Table name'[Date]).

And then sort this Month column and you will get the desired result.

[Idrissshatila]

Hello @cherrybi_com ,

it won't work like this because what you suggested would sort by month only so it would be like Jan 23 - Jan 24 beside each others, and he wants to sort by month year.

[Idrissshatila]

Hello @JennaExe ,

it won't be recognized as a date column, it will be recognized as a text.

If the issue is the ordering you can make a sorting for this text you're using  Sort = FORMAT (‘Table name'[Date], “MMYY”). then use it to sort the text column.

[JennaExe]

Hello, many thanks for replying - I don't generally use DAX as I don't understand how it works yet - would you be able to elaborate a bit on exactly what I need to do, as the formula you've suggested looks the same as the one I'm already using, except you've called it 'sort' - how does this make a difference, how does it work? Is this something I put in as another column, or instead of my one? Sorry for being so ignorant! Thank you!

[Idrissshatila]

Hello @JennaExe ,

I got you an easier approach, sort the column you made by date as in the screenshot

[JennaExe]

Thank you, but sorting the column in Reports doesn't impact on how the dates sort in my graphs, they are still alphabetical??

[Idrissshatila]

@JennaExe ,

it does impact.

[JennaExe]

It might on a table, but on my graph it's still like this: