lookup value based on measure

[Shearer]

Hi there :),

I have the following data:

Product	Month	Volume Forecast	Volume Actuals	Spend FC	Actual Spend
abc	01.2022		1		888
abc	02.2022		1		888
abc	03.2022		1		888
abc	04.2022		1		888
abc	05.2022		1		888
abc	06.2022		1		888
abc	07.2022		1		888
abc	08.2022		1		888
abc	09.2022		1		888
abc	10.2022	1		900
abc	11.2022	1		900
abc	12.2022	1		900
ccc	01.2022		1		222
ccc	02.2022		1		222
ccc	03.2022		1		222
ccc	04.2022		1		222
ccc	05.2022		1		222
ccc	06.2022		1		222
ccc	07.2022		1		222
ccc	08.2022		1		222
ccc	09.2022	1		250
ccc	10.2022	1		250
ccc	11.2022	1		250
ccc	12.2022	1		250

where I need to get the Actual Spend for the last month for which I have Volume Actuals, per product. I already created a measure to calulate the last month per product "Measure 1"= CALCULATE(MAX(table[Month]),FILTER(table, table[Volumes Actuals]=1)). I was thinking that if I use LOOKUPVALUE(table[Volumes Actuals], table[Month], Measure 1 it will work, but it doesn't :(.

Could you please help?

Thanks,

Shearer

[v-easonf-msft · Accepted solution]

Hi， @Shearer 

We cannot refer to a measure in LOOKUPVALUE function. You can use function ‘’calculate“ and ”filter“ to replace function ”lookupvalue“

Please try measure formula like:

_last month = CALCULATE(MAX('table'[Month]),FILTER(ALL('table'),'table'[Volume Actuals]=1),'table'[Product]=MAX('table'[Product] ))

_New Actual Spend = CALCULATE(MAX('table'[Actual Spend]),FILTER(ALL('table'),'table'[Product]=MAX('table'[Product])&&'table'[Month]=[_last month]))

Best Regards,
Community Support Team _ Eason

[Shearer]

Thanks @v-easonf-msft, this solves my issue (for now 😁). Take care