Plot multiple years of data on x axis on single chart

[Anonymous]

Hello everyone, 

Thank you in advance for helping me on this issue. So I am trying to plot multiple years of data (2019-2022) to see year over year changes in transaction counts. I have been using excel to do it and excel plots it correctly. However, when i try to use Power BI, and drill down to the lowest level of data heirarchy the graph becomes too long and need to scroll to view data (example pictures attached). I would like to see the graph as shown in excel with the x-axis labels showing months, with data granularity in days so that the graph fits the axis and display page. 

The graph I want (excel): 

The graphs in power BI that I am able to plot: 

Ideally I would like the first graph that I am getting in power BI shown above with the years superimposed on each other. What is the best way to do it? Thank you!

Best, 

Shubhankar

@Greg_Deckler @amitchandak @Ashish_Mathur @Jihwan_Kim @truptis @SpartaBI @tamerj1 

[Anonymous]

I'm trying to do exactly the same thing as OP. Has anyone found a solution for this?

[Ashish_Mathur]

Hi,

You should have a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Ensure there is a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the calendar Table.  To your visual, from the Calendar Table, drag Month name to the x-axis and Year to the legend.

[Anonymous]

Hi Ashish, 

Thank you so much for your prompt reply. 

I have all this setup exactly the way you asked and I have plotted the graph (see attached). However, this method would average the monthly counts whereas I want to see daily counts with the x-axis being displayed by months.  Basically I want the datapoints/tooltips to show daily counts but in order for the graph to fit the pane the x-axis needs to be monthly. Please see example from excel that I have attached to my original post. 

Best,

Shubhankar