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:
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
Anonymous, that's correct--the column order is reversed. It looks as if you're using the built-in date hierarchy instead of a custom hierarchy. I recommend creating a Date table, and creating a custom hierarchy in the Date table (this will allow you to specify the column order, resulting in the format you want).
DataInsights , thanks again. The issue I'm now having is that the format of the event start date is dd.mm.yyyy 00:00:00 and the date column in the new date table only seems to match midnight on a specific date. So if an event starts for example on 24.09.2019 at 00:00, then the date table has no problem with the data but if for example the event starts on 24.09.2019 at 08:30, then there's no match. Do you have tips on this please? I changed the data type of the event start date to just date and not date and time but there seems to be no change.
Thank you.