Forum Discussion
Comparing months from 2 years
Anonymous, try this solution:
1. Create a stacked column chart.
2. Create a year/month hierarchy and add it to Axis:
3. Add the Status column to Legend.
4. Add the measure to Values.
5. In the chart, click Expand all down one level in the hierarchy:
6. In Format --> X axis, turn off Concatenate labels:
Result:
Anonymous, try this solution:
1. Create a stacked column chart.
2. Create a year/month hierarchy and add it to Axis:
3. Add the Status column to Legend.
4. Add the measure to Values.
5. In the chart, click Expand all down one level in the hierarchy:
6. In Format --> X axis, turn off Concatenate labels:
Result:
HI,
Thanks. How did you create the year hierachy? Is this a separate table?
I have a column with event start date and then status. Do I have to create an extra column?
Could you pm your pbix with your test data if you don't mind?
Thanks again
Anonymous,
It's best practice to have a Date table in your data model. Once you create a Date table (with columns such as Year and Month Name), create a year/month hierarchy in the Date table. Be sure to create a relationship between the Date table and your data table.
Here's an article that explains how to create a hierarchy:
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
I'm nearly there, thank you. I would however like to have my chart like yours where you compare the months of the different years beside each other.
Is this because the order of the date hierachy in the axis is incorrect with the year at the top and the month below. I noticed, you had the month at the top and year below.
Thanks