Hello
There is a promotion in our store to get customers to buy products in certain product categories. I want to know how many customers bought these product categories during period selected by slicers and which sales agents were successful in persuading the customers to try new product categories for first time. New customers who are making their first purchase do not count, they have to be regulars trying the product categories for first time.
I have these tables:
invoices table with id, date, customer id, sales agent id,
invoice rows table with id, invoice id, product id, relation is invoice id to invoices table id,
customers table with id, name, relation is id to invoices table customer id,
product category table with product id, category id, relation is product id to invoice rows table product id
sales agents table with id, name, relation is id to invoices table sales agent id
Slicer by date is using inoices tables date.
I have been trying things for half a day but I am new to Power BI and nothing has worked.
I tried to divide the task to smaller chunks at first: get first invoice date for customer ever, then get first invoice date for product categories, then make column which returns 1 if first invoice date for customer is not the same as first invoice date for the product category and the first time customer bought the product category is between min and max date of dates slicer.
I only achieved getting the first ever invoice date for customer like this: CALCULATE(MIN(Invoices[Date])), this works.
Then I wanted to get minimum date for category like this:
CALCULATE(
MIN(Invoices[Date]),
FILTER('Product category', 'Product category'[ category id] IN {435, 436, 437, 438}
)
)
this does not work, it returns always the same value as the first one.
Any ideas how to approach this problem?
