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

[Peter_]

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

[affan]

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

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 · Accepted solution]

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

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

[Peter_]

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