Query prices for certain ID at maximum date

[Anonymous]

Hi community!

I am trying to build a measure to set a query for the prices [Cotacao] for the ID product [ID_FW]=21, [Validade]=1, in every segment[Prazo], for the last/max date [Data_Cotacao].

I am using this DAX formula:

calculate(average(Spot_FW[Cotacao]),LASTDATE(Spot_FW[Data_Cotacao]),filter(Spot_FW,Spot_FW[ID_FW]=21 && Spot_FW[Validade]=1))

The outcome should be in a line graph, the measure in Values, the [Prazo] in axis, as stated below:

However there are many segments [Prazo] that for the last date (25-05-2021) have [Validade]=0, though should not be in the graph (image: yellow signal). 

Any tip for what am I doing wrong?

Thanks in advance.

Cheers,

[Kumail]

Hello @Anonymous 

You can update the formula to 

MAxDateUpdate = 
CALCULATE(AVERAGE(data[Cotacao]), data[ID_FW]=21, data[Validade]=1, LASTDATE(data[Data_Cotacao]))

Let me know if this helps. 

Regards

Kumail Raza

[Anonymous]

No @Kumail , unfortunately I still have [Prazo] form another dates.

[Kumail]

@Anonymous 

Can you send screenshots of the error here.

Regards

Kumail Raza

[Anonymous]

the expression refers to multiple columns. multiple columns cannot be converted to a scalar value

[Kumail · Accepted solution]

@Anonymous 

The file is given below for your reference. 

https://drive.google.com/file/d/1iAkJAvLyQU_pvM-39RhX2yukjUbpLQZX/view?usp=sharing

I hope this helps or you can send the screenshot from data where the logic fails.

Regards

Kumail Raza

[Anonymous]

Got it @Kumail ! I rewrote the formula and it worked! 

Thank you so much!

Best regards!