cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shaunguyver Member
Member

Stacked Column Chart - Show month date hierarchy split by years

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Stacked Column Chart - Show month date hierarchy split by years

Hey,

 

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

 

image.png

 

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Stacked Column Chart - Show month date hierarchy split by years

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:

 

image.png

 

Hopefully, this is what you are looking for!

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
6 REPLIES 6
Super User
Super User

Re: Stacked Column Chart - Show month date hierarchy split by years

Hey,

 

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

 

image.png

 

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
shaunguyver Member
Member

Re: Stacked Column Chart - Show month date hierarchy split by years

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

Re: Stacked Column Chart - Show month date hierarchy split by years

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

Super User
Super User

Re: Stacked Column Chart - Show month date hierarchy split by years

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
Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
shaunguyver Member
Member

Re: Stacked Column Chart - Show month date hierarchy split by years

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

Super User
Super User

Re: Stacked Column Chart - Show month date hierarchy split by years

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:

 

image.png

 

Hopefully, this is what you are looking for!

Regards,
Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!