Customers who bought and not bought some product in last 90 days

[EvertonRamone]

I need a dax measure wich shows me which customer bought product B and C in last 90 days

And another one wich shows me those whose bought product B and C in last 90 days

(based in my filter date context)

Below is like it should be:

Can someone help me?

Here is a sample data if needed:

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

[v-juanli-msft]

Hi @EvertonRamone

1.Create a date table

Table = CALENDAR(DATE(2018,1,1),DATE(2018,12,31))

according to the dataset, DATE(2018,1,1) should be the min date, DATE(2018,12,31) should be the max date. 
You could modify them for your real dataset.

2.Create relationship between the date table and Calendar table

3. add the "date" column to a slicer, and select "relative" ->"Last","90",'Days"

4. Create a calculated column in the FactSales table

date = RELATED('Calendar'[Date])

5. Then create measures

bought last 90 days = IF(MAX([date])>=MIN('Table'[Date]),1,0)

bought B,C = IF(MAX([KeyProduct])=2||MAX([KeyProduct])=3,1,0)

if bought = IF([bought B,C]=1&&[bought last 90 days]=1,1,0)

count bought = CALCULATE(COUNT(Customer[Name]),FILTER(ALL(Customer),[if bought]=1))

count not bought = CALCULATE(COUNT(Customer[Name]),FILTER(ALL(Customer),[if bought]=0))

Best Regards

Maggie

[EvertonRamone]

@v-juanli-msft
I can't use this case.

When the user filter a date, example: 01/06/2018

The measure must look 90 days back and check in fact table if that custuomer appeared and bought product B or C, then 1 if yes and 0 if doesn't

I did something like this;

CALCULATE (
DISTINCTCOUNT(
Customer[CustomerKey);
DimProduct[Product] in {"A"; "B"}
FILTER(DimDate; DimDate[Date] >= MAX(DimDate[Date])-90 && DimDate[Date] <> MAX(DimDate[Date]))
)

But the problem is being looking back, it is not taking 90 days before the filter and is calculating wrong

[v-juanli-msft · Accepted solution]

Hi @EvertonRamone

I made some change to my previous solution.

Still add the "date" column from my created date table to the slicer, but set the slicer as "list" mode.

Then create a measure in the date table

Measure = DATEADD('Table'[Date],-90,DAY)

when you select "2018/8/1" in the slicer, the measure would show the 90 days before the "2018/8/1".

Then modify the measure bought last 90 days as below

bought last 90 days = IF(MAX([date])>=[Measure]&&MAX([date])<=MAX('Table'[Date]),1,0)

Best Regards

Maggie

[Anonymous]

I do not know how to use dax measure. I would solve it in this way. You can choose the time and then click what you are interested in.

[EvertonRamone]

Thanks @Anonymous, but I'll really need a measure like that