Stacked Column Chart - Show month date hierarchy split by years

[shaunguyver]

I have the following table of data loaded into PowerBI:

• Risk - (Text)
• Raised/Closed Date - (Date)
• Risk Type - (Text)
• Risk Impact £ - (Decimal Number)

The tables contains entries from 2017,  2018, and 2019.

I have created a stacked column chart displaying this data, with 'Raised/Closed' date as the Axis, and with date hierarchy set to month.

The issue is, this groups all months together regardless of year.

Is there a way to split up the chart to clearly show year? Or, to change the X-Axis to show 'Jan-18', 'Jan-19', etc?

[TomMartens · Accepted solution]

Hey,

I guess this pbix file contains what you are looking for:

To achieve the above it's necessary switch off the property "Concatenate labels", use the Year and Month column on the x-axis and "Expand all down ..."

Of course it's also possible to use just the column "Year-MM" from the Calendar table.

Hopefully this is what you are looking for.

Regards,

Tom

[TomMartens · Accepted solution]

Hey,

I guess this pbix file contains what you are looking for:

To achieve the above it's necessary switch off the property "Concatenate labels", use the Year and Month column on the x-axis and "Expand all down ..."

Of course it's also possible to use just the column "Year-MM" from the Calendar table.

Hopefully this is what you are looking for.

Regards,

Tom

[nikhildhavane]

Thanks TomMarteners , your solution is very helpful

[shaunguyver]

I'm trying to accept it as a solution, but my browser won't let me... apologies

[TomMartens]

You are welcome!
One of the powers of a Superuser, is accepting answers. I take your last post as a confirmation and will accept my post 😉

Regards,
Tom

[shaunguyver]

I got ahead of myself - how did you create the 'FieldListEntityCalculated' table? I'm unable to create on from fresh

[TomMartens · Accepted solution]

Hey,

not sure what you are asking for, but it's maybe how I created both of the tables ...

The Calendar table is created by a DAX statement, just mark the table and the DAX formula bar shows the DAX statement.

The table "fact" has been created by using the function "Enter data" that is available from the Report view (top icon on the left side).

Home (menu) --> External Data (ribbon) --> Enter Data. What might confuses you if you are not familiar with the function "Enter data", this works just once ... After the initial input, and most of the time I want to or have to add more data or delete data - editing a table is different. For editing data, it's necessary to enter Power Query, then hit the gear icon of the Source step of the table you want to edit:

Hopefully, this is what you are looking for!

Regards,
Tom

[shaunguyver]

You, sire, are a genius. Thanks ever so much!