cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Changing Month Number to Name - FORMAT not working

I'm trying to plot 2017 figures vs 2018's by month. This is working fine for Month Number on the X axis, using a calculated column simply as MONTH([Date]).

 

image.png

However I really want to see month name along the X axis. I tried using FORMAT(MONTH([Date]), "mmm") however it gives me this:

 

image.png

 

Seems to be converting the month number into 31st December 1899, 1st Jan 1900, 2nd Jan 1900 etc.

 

How can I get month names on the x axis?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Changing Month Number to Name - FORMAT not working

Hi @RyanS32229,

 

Two ways to extract month name from a date field:

 

MonthName = 'Table Name'[Date].[Month]

 

Alternatively

MonthName = FORMAT('Table Name'[Date],"MMM")

 

Best regards,

Yuliana Gu

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

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
Solution Sage

Re: Changing Month Number to Name - FORMAT not working

Hello I think your formula works fine.

 

You should sort MonthName by MonthNumber:

 

Please give it a try and let us know.

 

Best regards

Highlighted
Frequent Visitor

Re: Changing Month Number to Name - FORMAT not working

See the 2nd pic, it's clearly not working.

 

Using the FORMAT is taking MONTH([Date]) as a Day Number rather than a Month Number, therefore it's formatting January i.e. (01) into 31st December 1899, February into 1st January 1900, March into 2nd January 1900 etc.

Highlighted
Resolver III
Resolver III

Re: Changing Month Number to Name - FORMAT not working

Hi @RyanS32229

 

Can you perhaps share your data set? It could be that your dates are in the wrong format, or the setting in Power BI is set to a different date format also.

 

If you can send a link to your pbix I can have a look.

 

Thanks

 

shebr

Highlighted
Microsoft
Microsoft

Re: Changing Month Number to Name - FORMAT not working

Hi @RyanS32229,

 

Two ways to extract month name from a date field:

 

MonthName = 'Table Name'[Date].[Month]

 

Alternatively

MonthName = FORMAT('Table Name'[Date],"MMM")

 

Best regards,

Yuliana Gu

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors