Forum Discussion
Computing purchasing price fluctuation year over year
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
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
2 Replies
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
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
