cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vvanasperen
Advocate I
Advocate I

How do you display both years and months in x-axis?

Hi,

 

I've been trying to get this example working in Power BI. As the title says it's all about getting two (date) values in the x-axis. Is this possible and if so, how do I do it? I'm drawing a blank here. Thanks a lot.

 

Kind regards,

 

Vincent

2 ACCEPTED SOLUTIONS

You can create this in your data model.  For example you could have a new column in your date dimension that carries only the initial of the month and use that.  

 

However you won't see the years.  So I guess until the existing column chart is updated with more user control over the display of the axis - you could create your own custom control.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Thanks for the tips and workarounds. I've finally added three votes to an existing idea that revolves around the same issue. And in my opinion that will be the true solution to this issue. So if your question landed you on this forum post, I suggest you do the same and vote on that idea. 😉

 

Vincent

View solution in original post

14 REPLIES 14
Ed_Had
Regular Visitor

Hi,

You can try "Groups". It worked for me and it doesn't need additional measures:

Pic.PNGpic2.PNGpic3.PNG

 

Anonymous
Not applicable

I have chart in excel, in x-axis displayed Year, Week and Day, How I can do it in Power BI?

x-axis.PNG

Sikku
Frequent Visitor

 Dear Fernado, 

 

I hope you're problem got fixed up a long time back. Even now, you can cross check it with the following URL to create a simple Calendar Date table. It's really gonna fix your issue. 

 

Feel free to ask me up for any clarifications. 

Thank you and have a great day!

 

Best regards, 

 

Sikku Peter.

lyjust796
Regular Visitor

If you click the drill down on your visual (top left) to display all the variables ie day, month, year, then go to the Axis under fields and remove the quarter and day it should display what you are looking for. Worked for me, best of luck

Sikku
Frequent Visitor

Dear vvanasperen,


This question seems like a tough one, even though am not a developer, I wish the following answer may suffice your problem. Interestingly, the answer may seems a bit funny coz the solution is there inside the Power BI by itself. I hope the following solution steps would help you up to drill down into year on year analysis, quarter on quarter analysis and month on month analysis. Kindly, have a look at the steps mentioned below for your reference.


Here am going to compare the data of three year, three quarter and three months.


Year on Year Analysis


Step 1 : Take the 'Funnel' Chart (I've opted out) from the visualization section.
Step 2 : Add 'Fiscal Year' or 'Calendar Year' by dragging into visualization section (Beneath the 'Group').
Step 3 : Add 'Sales' or 'Total Sales' by dragging into visualization section (Beneath the 'Values').


Quarter on Quarter Analysis


Step 1 : Take the 'Waterfall' Chart (I've opted out) from the visualization section.
Step 2 : Add 'Fiscal Year' or 'Calendar Year' by dragging into visualization section (Beneath the 'Category').
Step 3 : Add 'FiscalYYY-QQ' by dragging into visualization section (Beneath the 'Breakdown').
Step 4 : Add 'Sales' or 'Total Sales' by dragging into visualization section (Beneath the 'Y-Axis').


Month on Month Analysis


Step 1 : Take the 'Clustered Column' Chart (I've opted out) from the visualization section.
Step 2 : Add 'Fiscal Month' or 'Calendar Month' by dragging into visualization section (Beneath the 'Axis').
Step 3 : Add 'FiscalYYYY-MM' by dragging into visualization section (Beneath the 'Legend').
Step 4 : Add 'Sales' or 'Total Sales' by dragging into visualization section (Beneath the 'Value').


Here the Power BI populates the report in the page. Sorry for not uploading any images to make it even easier for you, (Intermediate guy - Please, forgive me!


Kindly, check out and lemme have your thoughts on it.

Icad
Frequent Visitor

Dear Sikku, a picture, or better a pbix file, would be usefull.

Thx

Icad

kcantor
Community Champion
Community Champion

I use multiple years in my axis but I do it differently. I create a calculated column in my date table to combine my year and month into a single entry using this expression :Year-Month =[Year]&"-"&FORMAT([Month Number],"00"). This produces a date that appears as 2012-11 for November of 2012. I then use those as my axis. I also have a combined year-quarter and a year-week. This may not be exactly what you want but it is a working work around.





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

Proud to be a Super User!




ezabel
Regular Visitor

This works for me, but I'd like to have the month name instead of number. I made it that way, but then it can't be sorted ascending, do you know of a work around for this?

Sikku
Frequent Visitor

Dear Ezebel, 

 

Please, create a Calendar Date table by copying the content from the query and paste it in your 'Advanced Editor' (Inside the Query Editor > Home) to create one. When you've created it, you'd be able to see the Fiscal Month and Calendar Month in the table. Please, choose the Fiscal Month and drop it in the Shared Axis to view the month in the Month Format. It always starts with January ends and with December. 

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-2-fiscal-columns

 

Kindly, feel free to ask me up for any clarifications.

 

Sikku Peter. 

@kcantor: Thanks. That is a useable work around. I'm leaving this question open for a little while longer, just in case there is someone that know another way, besides your work around. 😉

Just make sure the value you are using in your x-axis is using a DateTime datatype.  Either specifiy this in your PowerBI model, or in your cube if you are using a cube.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark: I saw your answer, but if you look at the example in my first post it has months (by first letters of the month) and below that it has the years. I't not about drilling down, but about displaying a big timeline containing several years, but also being minimalistic. I haven't yet been able to do this with Power BI.

You can create this in your data model.  For example you could have a new column in your date dimension that carries only the initial of the month and use that.  

 

However you won't see the years.  So I guess until the existing column chart is updated with more user control over the display of the axis - you could create your own custom control.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Thanks for the tips and workarounds. I've finally added three votes to an existing idea that revolves around the same issue. And in my opinion that will be the true solution to this issue. So if your question landed you on this forum post, I suggest you do the same and vote on that idea. 😉

 

Vincent

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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors