Computing purchasing price fluctuation year over year

[KARIM-K]

Hi Everyone,

I am struggling with the calculation of purchasing price fluctuation (variance) when it comes to items that where discontinued in middle of year N and replaced by a new item which basically has the same functionnality than the item discontinued and therefore to calculate the price variance of item 2 we should consider the unit price of item 1 in year N-1.

Item ID	Item Desc	Quantity Year N-1	Unit Price N-1	Purchase Price N-1	Quantity N	Unit Price N	Purchase Price N	Price Variance
1	Item 1	50	1,2	60	10	1,3	13	1,00
2	Item 2	20	2,3	46	30	2,4	72	3,00
3	Item 3	0	0	0	20	1,35	27	3,00
4	Item 4	0	0	0	40	1,4	56	8,00
5	Item 5	0	0	0	25	1,6	40	0,00
	Total	70	1,51	106	125	1,66	208	15,00

In this example Item 1 was discontinued in Year N and replaced by Item 3 which was replaced later on by item 4. To calculate the price variance of Item 3 and 4, I need to consider that their unit price in year N-1 is the same as item 1 which is 1,2. Somehow, we needd an association between items 1 , 3 and 4

For Item 5 and since it is a new item, the price variance = 0.

I have tried to model this using Dax functions but without any success.

Any help please to figure out the approriate Dax function.

Best Regards,

Karim

[Anonymous · Accepted solution]

Hi @KARIM-K ,

Need an association between items 1 , 3 and 4. The data currently displayed is not enough to create an association.

Best Regards,

Jay

[KARIM-K]

Hi jay,

Thanks for your mail. If I add a new column stating the association between the 3 items does it help to figure out the solution and write down the appropriate dax function to get the appropriate result.

For the new Column, we can introduce a join item ID: 

Item ID	Item ID Join
1	1-3-4
2	2
3	1-3-4
4	1-3-4
5	5

Does it make sense ?

Best Regards,

Karim