Custom Date Format

[grahamfletcher1]

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.

 

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.

[v-yuezhe-msft]

@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

[grahamfletcher1]

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?

[v-yuezhe-msft]

@grahamfletcher1,

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


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

[grahamfletcher1]

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!

[v-yuezhe-msft]

@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