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

Change axis from month to mmm-yyyy format

Dear Power BI community,

 

I have a historical records of ticket sales in 2019 and 2020 and I hope to see the trends of these sales in a mmm-yyyy format. I have created a column chart, specifically I want to see the trend of the order quantity in months (in the 3rd figure). However, when I drill down my column chart to the months' level, I could not see the year which the ticket is purchased. Additionally, I tried to navigate around the platform but doesn't seem to be able to drill into such the mmm-yyyy format. Does anyone knows how I could change my x axis into such a format? Or could I do such arrangement at the Excel level?

 

drilldown(1).PNG

 

drilldown(2).PNG

 

 drilldown_but_wantchangetommyyyy.PNG

 

1 ACCEPTED SOLUTION

@andrewtan1993 

Use that expression to create a calculated column in the table instead of a measure





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
v-henryk-mstf
Community Support
Community Support

Hi @andrewtan1993 ,


According to your needs, although the "mmmm-yyyy" date field can be customized successfully, it cannot be used on the x-axis of the column chart. You can drill down to the month, and then turn off "Concatenate labels" in the x-axis of the Format, so that the trends of month and year sales can also be displayed visually. Maybe this is the best way.

 

v-henryk-mstf_0-1611194146556.jpeg

v-henryk-mstf_1-1611194154246.jpeg

 


If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PaulDBrown
Super User II
Super User II

@andrewtan1993 

You can use the function "expand all down one level in the hierarchy" option + turn off "concatenate labels" under the filter pane, x-axis formatting options:

concatenate labels.JPG

 

(You might need to srot the visual by the date fields once you have turned off the concatenate labels option)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






themistoklis
Super User II
Super User II

@andrewtan1993 

 

You can create a new column with this format.

See formula below:

 

Month-Year = FORMAT(Table[Date], "MMM-YYYY")

 

And use this column on x axis

Hi @themistoklis ,

 

Thank you for your suggestion. I tried using it but came back with this reply. Do you know how I could rectify this?

 

Error.png

 

@andrewtan1993 

Use that expression to create a calculated column in the table instead of a measure





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.