Visual Calculation using FIRST()

Lanceometer — Mon, 30 Dec 2024 09:38:31 GMT

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?

Re: Visual Calculation using FIRST()

ValtteriN — Mon, 30 Dec 2024 14:17:58 GMT

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/

topic Visual Calculation using FIRST() in DAX Commands and Tips

Visual Calculation using FIRST()

Re: Visual Calculation using FIRST()