Replacement Product Relationship & Calculations RRS feed

  • Question

  • Hello Everyone, I'm trying to build a powerpivot model that would allow for a "replacement product" relationship. Currently i have a Unique Product list, but the project requires that I maintain a relationship between a product, and the product that will be replacing it.

    Example: Product A and Product B are active products, but will soon be discontinued and replaced by similar Products C and D, respectively.

    Products A-D are all in the base product list, but I would like to be able to build a model that would allow me to calculate through the replacements. 


    I would like to calculate the stock replenishment levels of Product C, once active, based off historical usage (for simplicity's sake, lets say I would like to stock 1 month of usage, so Past 12 Months Usage/12). Since product C is brand new, I would need to be able to calculate the usage of both A and C through a powerpivot relationship, where perhaps in the product list, a second column of "Replaced by:" Item Numbers could exist, though not all items will have a replacement item.

    Is this sort of relationship possible? To add to the complexity, there exists the possibility of Product A and Product B both being replaced by Product C, which makes a one-to-one replacement relationship impossible.

    I hope I've provided enough example of what i'm trying to accomplish.



    Monday, June 11, 2018 9:53 PM


All replies