cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grahamfletcher1
Regular Visitor

Custom Date Format

I have a two parter around formating date fields for a matrix visual.

 

1) I have created a calculated column that uses the FORMAT function to create the date formatted as I need "MMM YYYY". This is working ok but it's not letting me sort the column by the actual date rather than the text version. Is there any way to keep this format but have it as a number not a text field?

 

2) I now want to flag some of the column headers in the matrix based on whether they are higher or lower than the date I have in another measure. This kind of works if I have the column formatted correctly then join an IF statement to return an extra string to add, it displays correctly with the added string but the columns that don't match the IF condition have an extra column with the added string and no data. I also tried doing the IF first and doing a different format using "MMM YYYY *", this doesn't work either. Does anyone have any ideas?

 

Below are my two attempts...

 

Month2 = IF([Control Date] > [MaxSubmittedMonth], FORMAT([Control Date], "MMM YYYY *", FORMAT([Control Date], "MMM YYYY"))

Month2 = FORMAT([Control Date], "MMM YYYY") & IF([Control Date] > [MaxSubmittedMonth], "*")

Here is the output of the second one which is closest.

 Custom-date-format.png

 

The MaxSubmittedMonth measure outputs 30/06/2017 which is correct and you can see Apr 2017 has two columns, one without the * and correctly shows the data, but then has an extra column with the * and no data. The Aug 2017 column correctly displays one column with a star and correct data.

5 REPLIES 5
v-yuezhe-msft
Microsoft
Microsoft

@grahamfletcher1,

Firstly, it is not possible to keep the calculated column "MMM YYYY" format and have it as a number. You can create another calculated column using formula: Month= Month(Table[Date]), then sort the first calculated column by this Month column. If you still get error, please post error message.

Secondly, you can create Month2 column using DAX below.

Month2 = IF('Table'[Control Date]>[ MaxSubmittedMonth],FORMAT('Table'[Control Date],"MMM YYYY"), FORMAT('Table'[Control Date],"MMM YYYY") & "*")



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

Thanks for your reply. Is there any way to get dates to format with the shorthand for the month name and keep it as a date? The options for date format don't have it in.

 

I've put your formula in and it gives me the same output as the second expression I gave with the duplicated column with no data for months that don't meet the IF condition. Any ideas?

@grahamfletcher1,

You can change format of date column as shown in the following screenshot.
1.JPG

The DAX works well in my PBIX file. Would you mind sharing me your PBIX file so that I can test? You can send me PBIX file via private message,

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yeah I found the formatting within the modelling tab but as I was saying there isn't one for MMM and the shorthand month name and also no option for custom. How come no custom like Excel?

 

Can you try in a matrix, I've just checked and the other visuals do seem to handle it, however as a column in a matrix it gives the duplication. pbix isn't mine to share, could replicate it for you though if needed?

 

Thanks for your help!

@grahamfletcher1,

You would need to use FORMAT() function, but format function will change the data type of column to Text.

I create a Matrix visual using Month2 Column, everything works well. Please share your PBIX file so that I can test.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors