Forum Discussion
Price Movements by sku and supplier (multiple variable) (% change)
Super UserIf 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.
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!
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)