Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Query prices for certain ID at maximum date

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].

 

graph6.JPG

 

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:
graph7.JPG
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,
 
1 ACCEPTED SOLUTION

@Anonymous 

 

The file is given below for your reference. 

Kumail_0-1624890929598.png

 

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

View solution in original post

6 REPLIES 6
Kumail
Post Prodigy
Post Prodigy

Hello @Anonymous 

 

You can update the formula to 

Kumail_0-1624882749735.png

 

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
Not applicable

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

@Anonymous 

 

Can you send screenshots of the error here.

 

Regards

Kumail Raza

Anonymous
Not applicable

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

@Anonymous 

 

The file is given below for your reference. 

Kumail_0-1624890929598.png

 

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
Not applicable

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

Thank you so much!

 

Best regards!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.