Personalized column date

[cristianml]

Hi,

I want to create a personalized column with a formula in the query editor for dates from another column. In this case I want to have a format of the date MMM-YY 

I have some error with the following :

Any ideas ?

Thanks !

[wdx223_Daniel · Accepted solution]

Date.ToText([#"[Month]"],"MMM-YY")

[wdx223_Daniel · Accepted solution]

Date.ToText([#"[Month]"],"MMM-YY")

[cristianml]

Hi @wdx223_Daniel ,

I try that buy I see two problems:

1° Is not showing the Year but YY.

2° When I changed data type to Date it gives an error:

What I need is to keep the format MMM-YY and Data Type: Date at the same time. 

[mahoneypat]

Use yy instead of YY.  Also, you could use your original formula in a DAX column (after your query loads).  Or you could just use a custom format string on your Date column.

Pat

[cristianml]

Hi @mahoneypat ,

The issue is that when I use DAX to format it gives me that format but if I use that in a pivot is in alphabetical order instead of date order. So if I change the data type to Date it changes the format and then cannot find that specific format in list of custom dates that I want that is mmm-yy

So is like I can't have both > Data Type: Date with format mmm-yy to use in the pivot in a date order:

Is there any trick for this ?

[mahoneypat · Accepted solution]

Actually, there are at least two ways to do that.

1. Add a Sort By column.  Make your date as an integer like yyyyMM so tht it sorts correctly and use that as a Sort By column for your formatted date column.

2. (recommended) - Create you column by making the start or end of month instead and leave it as a Date.  Then use a Custom Format String to display it as you like.

Creating a simple date table in DAX - SQLBI

Pat