Comparing months from 2 years

[Anonymous]

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

[DataInsights · Accepted solution]

@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]

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

[DataInsights]

@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/ 

[Anonymous]

@DataInsights 

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

[DataInsights]

@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).

[Anonymous]

@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. 

[Anonymous]

@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.

[DataInsights]

@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] )