Forum Discussion
Helper IIICalculate price evolution: which is the difference in yearly prices for a given project value
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.
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.
6 Replies
ryan_mayuSuper User
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
tomekmThank 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
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])
- 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.
- danextian
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!
