Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
Employee

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.