DAX - Inactive customers

[EvertonRamone]

 I have a measure like below wich returns me all active customers whose in last 90 days bought product category  = 'A'

CustomersActive last 90 days:=
VAR Last90Days=
    MAX (DimDate[Date) - 90
RETURN
    CALCULATE (
        DISTINCTCOUNT ( FactSales[CustomerKey]);
        DimProduct[Category] = "A";
        FILTER (
            ALL (DimDate[Date);
             DimDate[Date] >= Last90Day
                && DimDate[Date]  <= MAX (DimDate[Date])
        )
    )

Now I need to create a measure INACTIVE CUSTOMERS:

Rule:

1 - Make a list of all distinct customers whose someday bought product = "B" and the date of this was less than my actual filter date

2 - Create a measure using also a distinct count of the last 90 days, but comparing the first list to my actual

3 - If custumer was in the first list (product B) and in my last 90 days (product A) is not appearing, then 1, else 0

Thanks

[v-juanli-msft]

Hi @EvertonRamone

I have update my solution, could you have a look at it?

https://community.powerbi.com/t5/Desktop/Customers-who-bought-and-not-bought-some-product-in-last-90...

Best Regards

Maggie

[EvertonRamone]

Hi @v-juanli-msft, I marked your answer as a solution.

Could you help with this one? Is kind of different

[Greg_Deckler]

Do you have sample data for this?

[EvertonRamone]

@Greg_Deckler
Try this one:

FactSales
KeyDate	KeyCustomer	KeyProduct	Total
1	1	1	12,9
1	2	2	13
1	3	1	156,4
1	4	1	564,8
2	1	1	894,8
2	2	1	56,5
3	1	2	564,85
3	2	3	564,8
4	1	1	1325,6
4	2	1	132,3

Customer
KeyCustomer	Name
1	Jean
2	Mari
3	Lisa
4	Julian
5	Jhonny

Calendar
KeyDate	Date
1	01/01/2018
2	02/01/2018
3	01/05/2018
4	01/08/2018

Product
KeyProduct	Product
1	A
2	B
3	C

I'd like to see something like:

Customer	Inactive
Blabla	1
abcd	0
ggggg	1