Hi All
I have quite a complex problem and I’m not sure how to approach this or it’s even possible in Power BI. Apologies for the lengthy post but I’m not sure how else to best explain the scenarios.
I need to calculate product sales gained based on products identified during visits to customers compared to previous visits and any product that may have been ordered at the time of the most recent visit. It’s a little hard to explain so I have put together some scenario examples below which I’m hoping will help explain what I’m trying to achieve.
The required outcome is the need to be able to report on product gains by customer and product for selected periods. Identifying product losses would also be useful.
Some basic rules:
- A product is deemed to have been ‘gained’ if it did not exist on a shelf (a facing) on a prior visit but does exist in the most recent visit.
- A product is also ‘gained’ if it did not exist on a shelf on a prior visit, but an order for that product was taken.
- A product in NOT gained if it already exists on a shelf and an order is taken for the same product.
- The actual quantity on shelf or ordered is not so important - it’s more about the product gains.
Visit data is in a table (Visits) and sales are in a separate table (Sales). At present I have a couple of SQL Views that present the data as pseudo fact tables (FactVisits and FactSales) to Power BI - I don’t have any ETL logic building real fact tables right now.
The scenarios below relate to interactions with one customer.
Scenario 1: First visit to a customer and they have no existing products on the shelf - an order is taken. All ordered products = gain.
Scenario 2: Visit to a customer (first or otherwise) and products are discovered on the shelf. An order is taken for existing and new products. Only the ordered products that aren’t on the shelf are considered gains.
Scenario 3: Visit to a customer (first or otherwise) and products are discovered on the shelf. The products discovered differ to those identified on the prior visit. The products that did not exist on shelf in previous visit are considered gains. No order is taken in this example.
Scenario 4: Same as scenario 3 except an order is taken at the time of the most recent visit. The products that did not exist on shelf in the previous visit AND the products ordered that do not already exist on shelf are considered gains.
Here’s an image of the Power BI relationships in my test model. Unfortunately, I cannot upload this as it has confidential data. I can prepare an anonymized version if necessary.
I have full control over the SQL database and what can be loaded into Power BI.
Has anyone done anything similar or could offer some guidance on how best to approach this? I’d prefer to be able to do this in DAX if possible.
Many thanks in advance
Tiran