cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peter_ Regular Visitor
Regular Visitor

Matrix chart with dates in columns and one summary column at the end?

Hi,

 

How would I go about creating a Matrix type chart where there are N columns with cost for each month going back from today and one extra summary column showing % change of the current month since a month before ? For example:

 

--------| Cost October 2018 | Cost November 2018 | Cost Today | % change

Item 1 |

Item 2 |


I see two options which don't quite do the trick:

1) Put a date column into Columns field in a Matrix chart and Cost and % change columns into Values field. This enumerates both Values items for each date. I only need the % change to be there once and at the end. 

2) Create a measure for each month and put all in as Values. This does create layout I want, but it doesn't specify a date in the column name. 

 

Thanks

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
affan Established Member
Established Member

Re: Matrix chart with dates in columns and one summary column at the end?

@Peter_

 

As of now Power BI does not have any feature, you can vote for this idea on Power BI Ideas forum.

 

However I can suggest for a workarround where you can use card visual to be used as column labels.

 

You can use the following measures to create the labels

Label1 = "Cost " &  FORMAT(DATE(YEAR(NOW()),MONTH(NOW()),1),"MMMM") & " " & YEAR(NOW())
Label2 = "Cost " &  FORMAT(DATE(YEAR(NOW()),MONTH(NOW())-1,1),"MMMM") & " " & FORMAT(DATE(YEAR(NOW()),MONTH(NOW())-1,1),"YYYY")

You can see the image below for further clarity.dynamic labels.png

I have also uploaded the sample file which you can download for testing

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

View solution in original post

4 REPLIES 4
affan Established Member
Established Member

Re: Matrix chart with dates in columns and one summary column at the end?

Hi @Peter_

 

Your option 2 should work perfect. Once you are creating individual measure for each month then what issue are you facing to put the month name in the measure name?

 

Regards,

Affan

Peter_ Regular Visitor
Regular Visitor

Re: Matrix chart with dates in columns and one summary column at the end?

Hi Affan,

 

 

Doing that requires to rename each month's measure name each time we move onto a next month in the report so it doesn't really scale long term.

 

Peter

affan Established Member
Established Member

Re: Matrix chart with dates in columns and one summary column at the end?

@Peter_

 

As of now Power BI does not have any feature, you can vote for this idea on Power BI Ideas forum.

 

However I can suggest for a workarround where you can use card visual to be used as column labels.

 

You can use the following measures to create the labels

Label1 = "Cost " &  FORMAT(DATE(YEAR(NOW()),MONTH(NOW()),1),"MMMM") & " " & YEAR(NOW())
Label2 = "Cost " &  FORMAT(DATE(YEAR(NOW()),MONTH(NOW())-1,1),"MMMM") & " " & FORMAT(DATE(YEAR(NOW()),MONTH(NOW())-1,1),"YYYY")

You can see the image below for further clarity.dynamic labels.png

I have also uploaded the sample file which you can download for testing

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

View solution in original post

Peter_ Regular Visitor
Regular Visitor

Re: Matrix chart with dates in columns and one summary column at the end?

Thank you for the workaround Affan, that'll be perfect! 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)