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

[tomekm]

Hello,

I'm trying to calculate a "price evolution" calculation with a calculated column, so that for each project we calculate the difference between the first period (price 1) and its last period (where a value is available). My table looks like the following, and the last column is the expected outcome: 
- For project a, price 1 - price 2, divided by 2 (since there's only 2 year columns containing a 'price' value for this project), and then divided by price 1 column. Please advise. 

project	price1	price2	price3	price4	price erosion (percentage)
a	2	5			-0.75
b	3	4	5		-0.22
c	6	7	9	3	0.125

Thank you.

[danextian · Accepted solution]

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.

[tomekm]

It works. Thank you!

[Ashish_Mathur]

Hi,

Share some sample raw data to work with.  Share data in a format that can be pasted in an MS Excel file.

[ryan_mayu]

@tomekm 

you can select the first column and unpivot other columns in pq

then create a measure

Measure =

var _last=max('Table'[Attribute])

var _first=min('Table'[Attribute])

return (maxx(FILTER('Table','Table'[Attribute]=_first),'Table'[Value])-maxx(FILTER('Table','Table'[Attribute]=_last),'Table'[Value]))/COUNTROWS('Table')/maxx(FILTER('Table','Table'[Attribute]=_first),'Table'[Value])

pls see the attachment below

[tomekm]

Thank you, but I really need to be able to accomplish this using DAX only, and not through PQ. Any ideas of how to solve this? Appreciate it. 

[ryan_mayu]

@tomekm 

then you can try this

Column =

var _count=ISBLANK('Table'[price3])+ISBLANK('Table'[price4])

return SWITCH(TRUE(),_count=0,('Table'[price1]-'Table'[price4])/4/'Table'[price1],_count=1,('Table'[price1]-'Table'[price3])/3/'Table'[price1],('Table'[price1]-'Table'[price2])/2/'Table'[price1])