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:
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.
DataInsights Ok, it looks like I solved it. I created a new column in my original table [eventstartdate].[date] and then matched to date column in date table.
Thanks again.
Anonymous, glad you solved it.
For reference, here's the DAX for a calculated column that can be joined to the date column in the Date table:
Start Date = INT ( Event[Start Datetime] )