Short date issue


I set my file up using the below date table:

With “Month & Year” being my main date link. I am trying to use the “Short date” in my slicer, but unable to do so. I have tried to change the format of “Short date” in editor to “Date” (currently “ABC”) = error.

How can I fix this?

Kind regards


Hello @Stuart,

Thank you for posting your query onto the Forum.

Firstly if possible for you to change your date table, I would like to recommend and switch it to the “Date” table which is already available on our forum under the “M Code Showcase” created by @Melissa. Since this date table provides good number of options that one can have in their date table and it’s a compact one.

If not possible, here what you can do.

First Step: Duplicate your “Long Month” Column in the query editor.

Second Step: Split your duplicate created column using first 3 characters.

Third Step: Delete the other column part of the column.

Fourth Step: Duplicate your “Year” Column.

Fifth Step: Split your column by last 2 characters from the duplicate created column.

Sixth Step: Now delete the first part of your column which has been generated.

Seventh & Final Step: Merge your two columns and you’ll have your “Short Month Date with Year” column created.

Alternatively, you can achieve by adding columns in your table using the DAX in your date table -

First Step: Short Month = LEFT ( Dates[Long Month] , 3 )

Second Step: Short Year = RIGHT ( Dates[Year] , 2 )

Third Step: Short Month & Year = Dates[Short Month] & " " & Dates[Short Year]

Lastly, you can also combine these steps into one formula as given below to avoid multiple columns if you don’t require. Formula is as follows -

Short Month & Year = LEFT( Dates[Long Month ] , 3 ) & " " & RIGHT( Dates[Year] , 2 )

Hoping you find this useful. :slightly_smiling_face:

Thanks & Warm Regards,

1 Like

Managed to do that successfully, but now it is in the order alphabetically, how do I change this to Jan, Feb, Mar etc?

Hello @Stuart,

In your date table, you can try to sort your “Short Month & Year” column using “MonthYearNumber” column.

Thanks & Warm Regards,

1 Like