Handling multiple values when a single value is needed

[eddd83]

What happens when you select multiple months and/or years:

The resulting error message:

 

the measure:

Actuals less Forecast = 
VAR Today =
	MAX ( 'DateTable'[Date] ) 
VAR Month_End =
	EOMONTH ( TODAY (), 0 )
VAR SupplierProjMinusForecast =
	[Supplier Projected] - [Supplier Forecast]
VAR Year_Row = 
	VALUES ( 'DateTable'[Year] )
VAR Year_Today = 
	YEAR ( TODAY () )
VAR Month_Row =
	VALUES ( 'DateTable'[MonthNumber] )
VAR Month_Today =
	MONTH ( TODAY () )
VAR Date_Compare =	
	IF(
		OR(
			Year_Row < Year_Today,
			AND(
				Year_Row = Year_Today,
				Month_Row <= Month_Today
			)
		),
		SupplierProjMinusForecast,
		IF(
			Today <= Month_End, 
			//in Richard's volume report, user can select only a year or a quarter, so month check above fails. To avoid calculating very misleading variance, calc is limited to only producing a result if date selection is up to the end of the current month
			SupplierProjMinusForecast
		)
	)
Return Date_Compare

This line seems to be causing all of the problems when multiple months are selected.

VAR Month_Row =
	VALUES ( 'DateTable'[MonthNumber] )

How would I modify my measure, so that the visual functions properly?

[v-danhe-msft · Accepted solution]

Hi @eddd83,

Due to I could not reproduce your problem, could you please offer me some sample file to have a test or share the pbix file if possible?

Regards,

Daniel He

[eddd83]

I end up changing Year_Row and Month_Row from VALUES to MAX.

VAR Year_Row = 
	VALUES ( 'DateTable'[Year] )
VAR Year_Today = 
	YEAR ( TODAY () )
VAR Month_Row =
	VALUES ( 'DateTable'[MonthNumber] )

This seemed to produce the desired effect for my report.

[v-danhe-msft]

Hi @eddd83,

It's pleasant that your problem has been solved, could you please mark your reply as Answered?

Regards,

Daniel He