Price Movements by sku and supplier (multiple variable) (% change)

[Tak94]

Hello,

I am looking to track price movements over sku numbers and supplier. I have successfully created a dynamic index that will allow me to find the % change from the previous price while filtering for suppliers BUT I am not succesul in bringing it into a table where it returns the total increase averaged over the sku. I also have well over 1000 skus and supplier.

Here is what I have so far in terms of the measure:

average growth % = 
//setting the variables
VAR index = SELECTEDVALUE('Table'[Order])
VAR sku = SELECTEDVALUE('Table'[SKU])

//finding previousindex and previous price
VAR previousindex = CALCULATE(MIN('Table'[Order]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]>index))
VAR previousprice = CALCULATE(SELECTEDVALUE('Table'[ price ]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]=previousindex))

 //Delta % calculations                           
VAR delta = PRODUCTX('Table',1+CALCULATE(
                                (SELECTEDVALUE('Table'[ price ])-previousprice)/previousprice,
                                    FILTER('Table',previousprice>0)
                                        )
                    )-1
RETURN delta

My desired out put would be this where

1= -6.77%

2= 18.69%

3=  16.94% (AVG(2.88% from 123A & 31% from 456B)

4 = 1.4% (AVG(1.43% from 123A & 1.38% from 456B)

Highlight would be 5.96% (Average of 1 & 2)

* To Clarify my goal of using a measure instead of a calculated column I want to be able to adjust the index by filters. Therefore if I select just ABC for SKU 123A. The measure will remove orders: 13 &14. This means the % change on order 12 is then

(8.66-7.63)/7.63  = 13.5%. I know I can do calculated columns using earlier function but I will then loose the ability to dynamically calculate the previous price by applying new filters. 

SKU	Supplier	Order	Date	price	% change	1+% change	Product(1+% change)-1
123A	ABC	1	2/10/2020	7.85	0.00%	100.00%	-6.77%
123A	ABC	2	2/7/2020	7.85	0.00%	100.00%
123A	ABC	3	1/28/2020	7.85	0.00%	100.00%
123A	ABC	4	1/20/2020	7.85	0.00%	100.00%
123A	ABC	5	12/31/2019	7.85	0.00%	100.00%
123A	ABC	6	11/26/2019	7.85	20.03%	120.03%
123A	ABC	7	9/7/2019	6.54	0.00%	100.00%
123A	ABC	8	7/9/2019	6.54	0.00%	100.00%
123A	ABC	9	6/29/2019	6.54	0.00%	100.00%
123A	ABC	10	6/14/2019	6.54	0.00%	100.00%
123A	ABC	11	5/15/2019	6.54	-24.48%	75.52%
123A	ABC	12	3/24/2019	8.66	1.41%	101.41%
123A	XYZ	13	3/6/2019	8.54	1.43%	101.43%
123A	XYZ	14	12/6/2018	8.42	10.35%	110.35%
123A	ABC	15	11/16/2018	7.63	-5.57%	94.43%
123A	XYZ	16	10/26/2018	8.08	-4.04%	95.96%
123A	XYZ	17	10/16/2018	8.42
456B	ABC	1	1/31/2020	12.00	5.45%	105.45%	18.69%
456B	ABC	2	1/28/2020	11.38	0.00%	100.00%
456B	ABC	3	1/18/2020	11.38	0.00%	100.00%
456B	ABC	4	1/10/2020	11.38	3.36%	103.36%
456B	ABC	5	12/21/2019	11.01	4.86%	104.86%
456B	ABC	6	11/16/2019	10.50	0.00%	100.00%
456B	ABC	7	8/28/2019	10.50	0.00%	100.00%
456B	ABC	8	6/29/2019	10.50	6.60%	106.60%
456B	ABC	9	6/19/2019	9.85	0.00%	100.00%
456B	ABC	10	6/4/2019	9.85	0.00%	100.00%
456B	ABC	11	5/5/2019	9.85	-5.20%	94.80%
456B	ABC	12	3/14/2019	10.39	1.37%	101.37%
456B	XYZ	13	2/24/2019	10.25	1.38%	101.38%
456B	XYZ	14	11/26/2018	10.11	10.37%	110.37%
456B	ABC	15	11/6/2018	9.16	-5.47%	94.53%
456B	XYZ	16	10/16/2018	9.69	-4.15%	95.85%
456B	XYZ	17	10/6/2018	10.11

[amitchandak]

If you want to use earlier as suggested above by @Greg_Deckler , you can get next or previous price like that

Previous price = maxx(filter(table,table[SKU] = earlier(table[SKU]) && table[Supplier] = earlier(table[Supplier])
							&&  table[order date]  <earlier(table[order date])),table[price])
next price =minx(filter(table,table[SKU] = earlier(table[SKU]) && table[Supplier] = earlier(table[Supplier])
							&&  table[order date]  >earlier(table[order date]),table[price])

Based on one of these you can calculate change %. 

If you can put up a better example that would help.

[Tak94]

The problem with using a calculated column is that I want the results to be dynamic. What I mean by this is that if I start applying filters on the supplier or date, the previous value used in the calculation will update to the next. If I used a calculated column the previous value will remain the same. In the example below, it would mean order # 12's previous value will still be 8.54 even though I have removed this supplier.

Not sure if I can post a better example but maybe I can explain it better!

I want to be able to answer these 4 questions with the data I have and the answers equaling numbers in my original post (red in the screenshot)

1) What is my total % increase on sku 123A

2) What is my total % increase on sku 456B

3) What is my average % increase for supplier ABC

4) What is my average % increase for supplier XYZ

However, in practice I may want to be able to go into more detail. Lets say these skus are raw material like Aluminum or steel. My suppliers may also be all around the world (China, US, Europre etc). I may then want to pose the questions which country / region or which material has seen the largest increase. I would want my result to be based on the average of the total % increase of each sku.

So my current dax expression does this perfectly on the row level in the tables BUT fails to do so in the totals.  Maybe my header, "average growth" is miss leading abit. 

I modified my expression based on what @Greg_Deckler  suggested by using the ADDCOLUMN in the measure but I couldn't get it to work. I haven't used ADDCOLUMN much so maybe I am using it wrong. My results are identical. 

x = 
//setting the variables
VAR index = SELECTEDVALUE('Table'[Order])
VAR sku = SELECTEDVALUE('Table'[SKU])

//finding previousindex and previous price
VAR previousindex = CALCULATE(MIN('Table'[Order]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]>index))
VAR previousprice = CALCULATE(SELECTEDVALUE('Table'[ price ]),'Table'[SKU]=sku,
                        FILTER(ALLSELECTED('Table'),'Table'[Order]=previousindex))

 //Delta % calculations                           
VAR delta = ADDCOLUMNS('Table',"change",CALCULATE((SELECTEDVALUE('Table'[ price ])-previousprice)/previousprice,FILTER('Table',previousprice>0)))

RETURN AVERAGEX(SUMMARIZE('Table','Table'[SKU],"avg",PRODUCTX(delta,1+[change])-1),[avg])

If you need any more clarification, please let me know and I would be happy to explain further!

Thanks for the help!

[Tak94]

Here is a screenshot if I compare it to a % change from a calculated column. You will notice on order 12 it does not show the correct % change. It still uses XYZ which was filtered out for the purpose of illustrating this. The total I want on the bottom for this which is just SKU = 123A & supplier ABC is 2.88%

which is = PRODUCT ( 1+%change ) -1 OR = (1+.135) * (1+(-.2448)) * (1 + .2003) * (1 + 0) .... * (1+0)

[Greg_Deckler]

Having a bit of difficulty grasping this one exactly. But, if you can do this in a calculated column, you can do this in a measure. Just use ADDCOLUMNS and you can use EARLIER within a column that you add. The other trick is to store your table as a variable. Then you create a second table variable using ADDCOLUMNS that uses the first table variable as the first parameter. Then you can do fancy calculations over the original table within your second table.