How to show MonthNumber (1/2../12) instead of MonthName (Jan/Feb) in the x-axis in a bar char

[JasonXieee]

Dear PBI friends,

The date_time formatting is really driving me nuts. I think the so-called auto date/time intelligence function has changed my date column in the raw data into a date hierarchy, which is nice. But when I want to create a bar chart using the date column for the x-axis, it always shows the month name instead of the month number. I tried to change the format to <short date> already, but it is still the same. My question is:

1. How can I show the month as a number? Based on the guide from MS (https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time), the intelligence will create a column called MonthNo. Why don't I have this (just month as text)? I have only these four:

2. I saw many guidances - all saying that it is good to create a date table. Since I have this auto-date intelligence, do I still need that? I need to calculate "versus year-ago%" KPI later. There are so many ways of creating the date table, and I do not know which one is the common practice. It would be nice if you could share a link that shows the best way of doing that.

3. One other solution may be creating a new column using Month(), but how can I merge this new column to my auto-date hierarchy? Or in other words, put them into the x-axis and interact with other dimensions of a date.

Thank you so much!

Jason 

[Uzi2019 · Accepted solution]

Hi @JasonXieee 

Solution to your problem will be Calendar table/ Date table. 

https://www.youtube.com/watch?v=wN1ok8JDzrI

Try this solution.

Your Month No query and Year ago KPI query will be resolved with calendar table.

I hope I answered your question!

[Uzi2019 · Accepted solution]

Hi @JasonXieee 

You cant add Month No under date hierarachy. But you can create seperately.

Column= Month('Date')

Thi will only return Month number. but will be difficult to sort because of repeatative number.

In calendar table your Date value will be unique each row so no issue in sorting of this. in you scenario you Date table will act as a  role-playing dimension.

In your project if you have 3 seperate table you can connect with active or inactive relationship. but if your 3 dates in single table you can just connect 1 Date with your Main calendar table.

I hope I answered your question!

[v-qiaqi-msftv · Accepted solution]

Hi @JasonXieee，

Thanks for the reply from Uzi2019.

Let’s answer your questions one by one.

1). Actually, in the doc, there are calculated columns named MonthNo, which is not a auto-generated column by date hierarchy.

2). There is no need to create a single date table based on your scenario.

3). Yes, you are right, a calculated column with month number will do the trick.

Actually,  it is a common issue related to the auto Date/Time feature in desktop, it is easy to display the month number if you could create a calculated column to extract the month number from your Date Live field.

Create a calculated column, configure the DAX as below:

MonNum = MONTH('Table'[DATE])

Bar chart is not a good choice when you have something UI displaying need, here I suggest you switch the monthno column to Y-axis, which is much more calrified.

If you insist on putting the month number on the X-axis, you will have a chart including the month number starting with 0, which is a design issue of bar chart, that’s why I think it is not a good choice.

While, column chart will solve this design issue, if you could accept this kind of chart type.

Here is the doc for your reference:

Create a column chart in Power BI - Power BI | Microsoft Learn

I would be grateful if you could provide me with the pbix file or sample data.

If you have any other question please feel free to contact me.

[JasonXieee]

Thank you for answering my questions one by one! It is really helpful. I think now the best way is to create a Monthno column - actually, I would directly make a YEAR_MONTH column since the new column would not be able to interact with the YEAR in the auto-date hierarchy (if you know how, please let me know!).

It is so nice that you also share the watch out on the bar chart. I use bar charts a lot to present changes along the time period (I think it's even better than line charts). But I will also test the column chart to see which is better.

[Uzi2019 · Accepted solution]

Hi @JasonXieee 

Solution to your problem will be Calendar table/ Date table. 

https://www.youtube.com/watch?v=wN1ok8JDzrI

Try this solution.

Your Month No query and Year ago KPI query will be resolved with calendar table.

I hope I answered your question!

[JasonXieee]

Thanks for your quick response, Uzi.

The reason why I hesitate to create a calendar table is that I have three kinds of dates for each project line. If I linked that calendar table to all of them, they would impact each other (like during filtering).

Do you have any idea how I can create one more column as MonthNo in the date hierarchy? 

[Uzi2019 · Accepted solution]

Hi @JasonXieee 

You cant add Month No under date hierarachy. But you can create seperately.

Column= Month('Date')

Thi will only return Month number. but will be difficult to sort because of repeatative number.

In calendar table your Date value will be unique each row so no issue in sorting of this. in you scenario you Date table will act as a  role-playing dimension.

In your project if you have 3 seperate table you can connect with active or inactive relationship. but if your 3 dates in single table you can just connect 1 Date with your Main calendar table.

I hope I answered your question!

[JasonXieee]

Thank you! I learned a lot from your answer as well. Let me test them.

[Uzi2019]

@JasonXieee 

happy to help!