Visual Calculation using FIRST()

[Lanceometer]

Hi Folks,

I have data for some suppliers and prices for a material:

Supplier		Date		NetPrice
SUP1		28.02.2024		20,66
SUP1		29.02.2024		20,66
SUP2		01.03.2024		16,95
SUP2		02.03.2024		16,95
SUP2		03.03.2024		16,95
SUP2		04.03.2024		16,95
SUP2		05.03.2024		19
SUP2		06.03.2024		19

I want to show where the price was going up or down per supplier.

The user wants to see this in an indexed line chart

something like this:

you can see that also the supplier changed

so it is basically calculated 

Calculation = [Average of NetPrice] / FIRST([Average of NetPrice])

But when I do this in a visual calculation it doesn't work. Apperantly FIRST() does not respect the legend (even with the parameters for sorting blank values last). So it takes the first date of all suppliers. but for my second supplier the first value is then blank. this is why i get infinity values

Any ideas on that?

[ValtteriN · Accepted solution]

Hi,

As seen in the visual calculation table supplier appears twice in your filter context. Because of this it is easier not to use visual calculations in this case, since calculating first date for a specific supplier will be challenging. With normal dax you can e.g. create something like this:

first net price division =

var _s = SELECTEDVALUE('Table (47)'[Supplier])

  VAR _firstDate = CALCULATE(MIN('Table (47)'[Date]),ALL('Table (47)'[Date]),'Table (47)'[Supplier]=_s)

  RETURN

  DIVIDE([Average of NetPrice],

CALCULATE(

[Average of NetPrice],ALL('Table (47)'[Date]),'Table (47)'[Date]=_firstDate))
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/