I’m after some guidance on how to measure product gains & losses over time - I’ve realized that it’s more complex than I thought!
We capture whether products exist on customer premises based on a visit to the customer by a sales rep. Customers can also place an order during the rep visit. I need to be able to identify which of these products were gained or lost over time by customer and product.
Here’s a sample of a typical timeline.
- The yellow cells indicate a gained product. If an originally gain product appears in subsequent visits it is not counted again.
- The red cells indicate a lost product.
- Note: if a product is lost but the next visit shows it present again, the previous loss is regarded as a temporary out of stock - not another gain.
I have attached a .pbix containing some test data.
This has a couple of matrices which show the visit data and order data - stored in separate tables (the visit matrix does not show the visit on 27/8/2020 because no products were listed so only a header record exists - how can I show visit?
I’m assuming I need to combine both the visit and order tables, into a virtual table perhaps, and iterate through this working out for each visit/order whether a gain or loss was achieved. However, I really don’t know how to start with this - I’m struggling to visualize how to combine the data in such a way that I can work this out.
Any help would be appreciated.