Forum Discussion

tjlaine's avatar
tjlaine
New Member
3 years ago
Solved

Matrix to hide months without data

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

 

 ActualActualForecastForecastForecastBudget
 JanFebMarAprMayJun
Sales100120    
COGS8060    

Margin

2060    
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!