cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Microsoft
Microsoft

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.

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.






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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors