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

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

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

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

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!

vvanasperen Frequent Visitor
Frequent Visitor

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

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

14 REPLIES 14
kcantor Super Contributor
Super Contributor

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

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 Datanaut!




Highlighted
vvanasperen Frequent Visitor
Frequent Visitor

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

@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. ;-)

Phil_Seamark Super Contributor
Super Contributor

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

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!

vvanasperen Frequent Visitor
Frequent Visitor

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

@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.

Phil_Seamark Super Contributor
Super Contributor

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

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!

vvanasperen Frequent Visitor
Frequent Visitor

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

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

Sikku Frequent Visitor
Frequent Visitor

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

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.

lyjust796 Frequent Visitor
Frequent Visitor

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

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

Icad Visitor
Visitor

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

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

Thx

Icad

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 178 members 2,418 guests
Please welcome our newest community members: