Dynamic Measures based on filter selection

[Anonymous]

I have Sales Column and I created a YTD measure using that column. I want to accomplish the following.

Show YTD or Sum of Sales when a date value is selected. Is there a way to accomplish this, like create a dummy filter(like groupby) with values YTD and Monthly and when date value is selected with one of these group by filter values it would select corresponding value. For Ex in my case when YTD is selected I want see YTD measure and sum of sales when Monthly is selected.

[Anonymous]

I just finished a straight hour of writing a series of measures that do exactly this. Here's my method:

Use "Enter Data" to create a new table. Call it something like "MeasureBehavior" and make a column called "Stat". You can call it whatever you want, but that's what I call it. Stat is the name of the method you want to use, so Monthly, YTD, whatever else...

Then your measures will take the following form:

Measure = IF(
	HASONEVALUE(MeasureBehavior[Stat]),
	SWITCH(
		FIRSTNONBLANK(MeasureBehavior[Stat], 1),
		"Monthly", SUM(TableName[ColumnName]),
		"YTD", CALCULATE(
			SUM(TableName[ColumnName]),
			DATESYTD(DateTable[Date])
		),
		BLANK()
	),
	BLANK()
)

The BLANK() functions are defaults for when conditions are not met. The first one is if there is only one stat selected but it's not either Monthly or YTD. This condition should never actually happen but I think it's best to give SWITCH a failsafe blank as a general rule. The last BLANK() is for cases when more than one stat is selected (or when the slicer has no selection, which is the same thing). You may prefer to use the plain monthly sum here, so the default will always be monthly unless YTD is explicitly selected. Up to you.

Oh, then of course you would add a slicer for MeasureBehavior[Stat] to the report.

[Vvelarde · Accepted solution]

@Anonymous

1. Create a New Table with

Calc

YTD

Monthly

2. Then Use a Slicer with this column

3. Create a measure

Sales=Switch(Values(Newtable[Calc]),"YTD",MeasureofYTD,"Monthly",MeasureofSumSales)