create measure to define new customers with specific criteria

[Anonymous]

I am new to Power BI, this problem is a little beyond my capability right now, I tried to look for some solutions but nothing really fits what I want.

So I have a "order table" like this

Date	Order ID	Customer ID	Product ID	Sales
01.01.2021	1	01	A	15
01.01.2021	2	02	A	31
01.01.2021	3	03	A	59
01.02.2021	4	02	A	31
01.02.2021	5	03	A	56
01.02.2021	6	03	B	13
01.02.2021	7	04	B	68
01.03.2021	8	03	B	49
01.03.2021	9	01	B	45

Basically we launched product B in Feb, I would like to know the impact of B on A.

So I want to create a measure to find out: for each month, how many existing customers (e.g. 01, 02, 03) who have bought product A in the past, made a purchase on B in the current month. 

This means they are not new customers that never bought anything before, like customer 04 in Feb; or existing customers that have bought product B in the past, like customer 03 in Mar. 

In addition to COUNTROW, I would also like if it can act as a filter, so if I click on it, another customer visual will show who these customers are, so I can see how much they spent in the past and now on product A.

Thanks a bunch in advance!

[V-lianl-msft]

Hi @Anonymous ,

Try to create a measure and apply it to visual level filter:

Test = 
VAR COUNT_ = CALCULATE(DISTINCTCOUNT('Table'[Product ID]),FILTER(ALLEXCEPT('Table','Table'[Customer ID]),'Table'[Product ID] in {"A","B"}))
RETURN IF(COUNT_>1&&MAX('Table'[Product ID])="B",1,0)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you this is almost perfect! For product B, they are actually named differently, so I have product B1 and product B2, do you know how  to add it onto your solution? 

[tackytechtom]

Hi @Anonymous ,

How about this:

Here is the measure I used:

TomsMeasure = 
VAR _custBoughtProdA = 
SUMMARIZE (
    FILTER ( Table, Table[Product ID] = "A" ),
    Table[Customer ID],
    "Sales", SUM ( Table[Sales] )
)
RETURN
CALCULATE (
    DISTINCTCOUNT( Table[Customer ID] ),
    FILTER ( Table,  Table[Product ID] = "B" ),
    FILTER ( _custBoughtProdA, [Customer ID] = Table[Customer ID] )
)

Let me know if that got you closer a solution!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Thank you so much for the answer.

However with this solution, if a customer has bought product B in previous month, he will be counted again if he bought product B in the current month. 

Is it possible to exclude customers who have bought product B in the previous months in your solution?