Forum Discussion

grahamfletcher1

Regular Visitor

8 years ago

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 i...

Anonymous

Not applicable

8 years ago

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
Regular Visitor
8 years ago
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?
- Anonymous
  Not applicable
  8 years ago
  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
    Regular Visitor
    8 years ago
    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!