Matrix to hide months without data

[tjlaine]

I want to create a matrix visual that has following stucture:

	Actual	Actual	Forecast	Forecast	Forecast	Budget
	Jan	Feb	Mar	Apr	May	Jun
Sales	100	120
COGS	80	60
Margin	20	60
Margin %	20 %	50 %

Matrix rows (on the left) come from individual table and all the measures are actually text values because the last one I must show as percentage. I use switch formula to create this. Matrix columns are hierarchy based on individual table that has text values "Actual", "Forecast" and "Budget" and also month names from my calendar table.

I want my end user to select a month that represents the last month that is used for actuals. Next my end user selects how many months is shown for forecast using a slicer that has values from 1-12. Budget will be shown automatically depending how many months are still available for the current year.

For example: end user selects May for actuals and 4 months for forecast.

Actuals 1-5

Forecast 6-9

Budget 10-12

Everything would be working already with only months on the columns but my end users like to know what type of information is shown for each month. I tried all sort of conditional formatting options and creating new tables but I had no luck.

Below you see where I'm at this point. I've got everything to work but I can't hide months that doesn't have values. My matrix shows all months for actuals, forecast and budget. Values are shown correctly for months that follows my selection, others are blank. Trying to filter my measure with blank or empty value doesn't work and I haven't selected "show items with no data" for months.

 

Can anyone give me a hint how to solve this or push me to the correct path? All tips and tricks are appreciated!

[tjlaine]

Thank you for the tip @parry2k 

After changing my measure to the "Dynamic" everything worked like a charm!

[parry2k · Accepted solution]

@tjlaine First you don't need to create your measures to text to show different formats, you can use dynamic formatting From Thousands to Billions: The Power of Dynamic Formatting in Power BI - April 2023 Power BI Update...

Can you share your measures expression?