Select a value from a column based on another value from column.

[AThom]

I would like a measure that will select the the [Target -21/22] value based on the last value in the [21/22 - YTD] column.

Can I get help with DAX that would acheiev this?

[m3tr01d]

Hello @AThom 

Can you try this :

Measure = 
CALCULATE(
	MAX( YourTable[Target - 21/22] ),
	TOPN(
		1, 
		VALUES( YourTable[Date] ),
		Table[Date]
	)
)

[AThom]

Hi,

That doesn't work. I am trying to get a measure that gets the corresponding [Target -21/22] value based on the MAX or most recent [21/22 - YTD] value. 

[m3tr01d · Accepted solution]

Ha yes, indeed

what about this approach instead :

Measure = 
VAR _Lastdate = 
CALCULATE(
	MAX( YourTable[Date] ),
	ALL( YourTable[Date], YourTable[Month] ),
	YourTable[21/22 - YTD] > 0
)

RETURN
CALCULATE(
	MAX( YourTable[Target - 21/22] ),
	YourTable[Date] = _LastDate
)

[AThom]

Thanks a lot, that worked