Daily average for latest date with two filters

[Anonymous]

Hi PBI folks!

I'm trying to figure out a measure to calculate the average price, on the last date available for two specif IDs (ID_SPOT=30 and ID_TIPO_VALOR=8).

The table is:

And my output should be the average of column VALOR, for the latest date where the previous IDs were filtered (ID_SPOT=30 and ID_TIPO_VALOR=8).

Can you help me out, please?

Thanks!

Cheers

[Anonymous]

Hi @amitchandak ! Thanks for your help, it worked! I gave a go for the first option.

Best regards!

[amitchandak · Accepted solution]

@Anonymous , try new measure like

measure =
var _max = maxx(filter(allselected(Table),Table[ID_SPOT]=30 && Table[ID_TIPO_VALOR]=8), [Data_cotaCAO])
return
AverageX( filter(Table,Table[ID_SPOT]=30 && Table[ID_TIPO_VALOR]=8 && table[Data_cotaCAO] =_max ), [Valor])

or

measure =
var _max = maxx(filter(allselected(Table),Table[ID_SPOT]=30 && Table[ID_TIPO_VALOR]=8), [Data_cotaCAO])
return
AverageX( filter(allselected(Table),Table[ID_SPOT]=30 && Table[ID_TIPO_VALOR]=8 && table[Data_cotaCAO] =_max ), [Valor])