Apply unique slicer to specific matrix value

[PBIUser4321]

I’m new to PowerBI and it is an excellent tool! My employer has a monthly reporting PowerBI dataset that is available to certain employees.

I use this existing dataset to create tables/matrices that go into my presentations. I am not actually creating any datasets.

One of my matrices has 6 unique values that are calculated based on the current month data. This matrix is easy to create by using a slicer to select only the most recent month.

One of the 6 value fields is a “% of total” value. The issue I am running into is that I would also like to display comparative % data from the same month in the year prior.

Below is a simplified idea of what I am looking for, conceptually, but keep in mind that there would also be several other value columns in the actual matrix that would be displaying values as of April 2024:

	April 2024 count	April 2024 distribution	April 2023 distribution
Red	50	25%	30%
Blue	50	25%	30%
Green	50	25%	20%
Yellow	50	25%	20%
Total	200	100%	100%

Is it possible to create a single value column based on March 2023, when everything else in the matrix relies on the March 2024 slicer?

[Kaviraj11]

You can filter out the colour :

CALCULATE(
	[Measure],
	DATEADD(
		'Calendar'[Date],
		-1,
		YEAR
	)
,FILTER('Tablename',not columnname in {"orange","purple"})
)

[PBIUser4321]

Ok, this is a sound workaround, but is likely not practical for my solution.

It is a large dataset (which unfortunately I have no direct control over) so I acknowledge there may not be any work around. I will give some more context just in case. I am actually using 3 different slicers to get down to the matrix data I want.

In this case one of the slicers zeroes in on a specific client. So instead of colours, lets consider the row in my matrix is American states. And I want to use a client slicer, a date slicer, etc.

If I select Client A, there are data points in California and New York (and the other 48 states hidden were previously hidden). Then client B has data points in 5 states (all else hidden), etc. etc. But, in fact, all 50 states exist somewhere within the dataset.

My new measure value column indeed has the correct data, but it shows show all 50 states now for every client (with zeroes for the remaining 48 states on Client A, etc).

Does this make sense? Any workaround at all? I appreciate the effort you've made either way! Thanks.

[Kaviraj11]

Please try to create this measure.

CALCULATE(
	[Measure],
	DATEADD(
		'Calendar'[Date],
		-1,
		YEAR
	)
)

[PBIUser4321]

Thanks, with your help I'm very close now, I've got the new value column, and the results are correct! but it has added some 0% rows for this value.

To go back to my colour example from the last message where there were four colours (red, blue, green, yellow) the matrix now also shows orange, purple, etc as 0% in this column (where these rows were previously filtered out due to being zero)

Appreciate your help so far!