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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Show short month names on axis

Hi, I have a small line graph on my dashboard with dates on the x-axis. Now the names of the months take too much space. How can I use shorter names?

 

calls.JPG

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Anonymous,

 

Add a column to your table with the following formula:

 

Short_Name = Format (Table[Date], "mmm")

Then use this column as you axis.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

20 REPLIES 20
smit3262
Regular Visitor

Is anyone else frustrated with the way Microsoft has done dates in PowerBI visuals? (Among other formatting issues) PowerBI is excellent for manipulating data but the presentational aspect is awful. When developing a report I should never have to write any Dax or manipulate any data to reformat visuals. Increased support for ailiasing fields for specifc visuals and general freedom for formatting fields on the fly without having to manipulate underlying data would be a massive improvement. 

 

To summarize: The fact that I have to go into an underlying table and figure out a complicated sorting framework that considers data crossing multiple years to make "January" say "Jan" in my visual is a waste of time and is stupid.

Anonymous
Not applicable

Hi I tried several method, but could not find a solution. Kindly assist if you found a solution !

 

Methods tried :

1. Creating a duplicate date column and setting that column format as "mmm" ---> result : seems to work on tables but on a bar chart it shows Jan 2021 or"mmm yyyy"

 

2. Creating a column with this formula : Short_Name = Format (Table[Date], "mmm") ---> result : month is sorted in ascending order instead of date. e.g. April, August, XXX  instead of Jan, Feb Apr, XXX

MFelix
Super User
Super User

Hi @Anonymous,

 

Add a column to your table with the following formula:

 

Short_Name = Format (Table[Date], "mmm")

Then use this column as you axis.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

That works! But now my axis is sorted as Apr, Aug, Feb, etc.... How do I order by date?

 

sortby.JPG

Has anybody come up with a solution for this? 

 

I tried to:

 

1. Convert the new column to DATE type but there is no DateTime format that it can use so it has to be TEXT

2. I tried to use "Sort by Column" but that didn't work with the date I was using on the X-axis

3. I tried to generate a rank for the new shorter month names by using SWITCH:

OrderDateMonthNumber = SWITCH('Sales SalesOrderHeader'[OrderDateMonth], "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May"  
               , 5, "Jun", 6, "Jul", 7, "Aug", 8, "Sep"  
               , 9, "Oct", 10, "Nov", 11, "Dec", 12) 

and then use "Sort by Column" but that did not work either. 

 

This is such a simple little problem, there has to be a way of sorting the months correctly somehow.

Hi,

Create a new columb with the formula

Datesort = FORMAT('Sales SalesOrderHeader'[Date]; "YYYYMM")

The format as number. And order your short month column by this.column.

Regards
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks @MFelix but that doesn't work either. I'm really struggling to understand how that "Sort by Column" feature is supposed to work, I've had a lot of problems with it.

 

Here's a screenshot, does it look right?:

 

PowerBI - Date Sort (2018-03-31).png

 

I'm starting to think that maybe I need to create some of these columns in M instead of DAX. There might be somekind of a dependency error thing going here that "Sort by Columns" isn't displaying me.

You can create the column in dax you need to.sort the.column of the short month by thus.column. The inage you have is.correct the colun you select is incorrect.

Regsrds

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix Which column should I choose for "Sort by Column"?

The monthsort column.

So you will sort month short name by monthsort column

Regards

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Took me a little while to understand how this is meant to work. MFelix is correct but I thought I'd provide a complete answer to both questions that should hopefully make it easier for others.

The problem is that a three letter month will be sorted alphabetically. We need to create another column that it can sort numerically.

Create two columns:

Short Month = FORMAT( [Date], "mmm" )
Month Number = MONTH( [Date] )

Click [Short Month] in the fields panel and then, on the ribbon, under the 'Modelling' tab, click 'Sort by Column' and sort by [Month Number]. This should sort the months correctly.

Capture3.PNG

 

Glad this has helped so many people! 😊

Thanks for saving our time !!!!

Anonymous
Not applicable

Thank you, your explanation step by step helped me a lot!

Anonymous
Not applicable

Thank you! I encountered the same problem and I used your method, it works!

 

JUJU_628
Microsoft Employee
Microsoft Employee

thanks! has anyone tried the Format recently? I dont think this code works anymore. 

All I get is the error message:

Expression.Error: The name 'Format' wasn't recognized. Make sure it's spelled correctly.

Hi @JUJU_628,

 

The Format syntax is DAX code so it's to be used on the front end of PBI. 

 

Looking at you error message it appears to me that you are using it on the query editor that uses M Language. 

 

To get month name on the query editor you need to use Date.MonthName. 

 

https://docs.microsoft.com/en-us/powerquery-m/date-monthname


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I also need to know how to sort them chronologically!

Omega
Impactful Individual
Impactful Individual

Try reducing the X-Axis text size. 

 

Click on the chart -> Click on Format -> Click on X-Axis -> Reduce Text Size to 8 (for example)

 

X-Axis format.PNG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.