Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shaunguyver
Helper III
Helper III

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
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

7 REPLIES 7
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks TomMarteners , your solution is very helpful

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

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


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.