Calculate price evolution: which is the difference in yearly prices for a given project value

Hi tomekm 

Try the following:

Price erosion = 
-- Capture the value from the 'price1' column of the table.
VAR _price1 = tbl[price1]

-- Create a filtered table '_tbl' using SELECTCOLUMNS, assigning indices (1, 2, 3, 4) to prices.
-- The SELECTCOLUMNS function assigns column names ("Index" and "Price") to the two columns.
-- Then, the FILTER function removes any rows where 'Price' is blank.
VAR _tbl =
    FILTER (
        SELECTCOLUMNS (
            {
                ( 1, tbl[price1] ), -- Assign 1 to 'price1'
                ( 2, tbl[price2] ), -- Assign 2 to 'price2'
                ( 3, tbl[price3] ), -- Assign 3 to 'price3'
                ( 4, tbl[price4] )  -- Assign 4 to 'price4'
            },
            "Index", [Value1],  -- 'Index' column represents the position of each price
            "Price", [Value2]   -- 'Price' column represents the actual price values
        ),
        NOT ( ISBLANK ( [Price] ) )  -- Filter out rows where 'Price' is blank
    )

-- Combine the non-blank prices into a string separated by "|" using CONCATENATEX, 
-- and sort the prices by 'Index' (ascending order).
VAR _concat =
    CONCATENATEX ( _tbl, [Price], "|", [Index], ASC )

-- Calculate the length of the concatenated string (number of price values).
VAR _len =
    PATHLENGTH ( _concat )

-- Extract the latest (last) price from the concatenated string.
VAR _latestprice =
    PATHITEM ( _concat, _len, 1 )

-- Calculate the difference between the first price (_price1) and the latest price (_latestprice).
VAR _diff = _price1 - _latestprice

-- Return the price erosion calculation as the difference divided by the length of the prices,
-- and then divide by the initial price (_price1) to normalize the value.
RETURN
    DIVIDE ( DIVIDE ( _diff, _len ), _price1 )

Note: Index in the _tbl variable is important as CONCATENATEX combines values by ascending order if no order column is specified.