Forum Discussion
Comparing months from 2 years
hi,
I've 2 columns in a table where I'd like to compare data. The columns are named event start date and status. Status contains data like registered, cancelled etc. The event start date has the format date/time.
How can I compare data for months over seperate years? Should I create a column called status this year and then status last year? Just not sure which way to go.
I've created the following stacked column chart below but I can't separate it in terms of years.
Thanks for any feedback
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:
8 Replies
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,
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/