Looking for a filter solution

[leilei787]

Hello:

i have a question about setting up a filter, wonder if anyone can help! thanks in advance!

i have the following table below. Customer Name, Product and Purchase Date. ( Other columns are what results should look like if the filter is selected)

so i need to setup a filter with 3 buckets when product 1001 is purchased

1) who has purchased product 1001 within 90 days (active customer)

2) who has NOT purchased product 1001 since 90 days from today ( cut off date is 1/28/22, 90 days from today)

3) who has NOT purchased product 1001 since 180 days from today (cut off date is 10/30/21, 180 days from today)

The 90 days bucket should also include customers who have not purchased for 180 days ( so filter 3 is a subset of filter 2) 

For example, today is 4/28/2022. so 90 days cut off is 1/28/2022, 180 days cut off is 10/30/2021. 

~Customer A, purchased 2/1/2022, so it is an active customer

~Customer F, purchased 9/1/2001, so this customer is in that 90 days but also in the 180 days buckets ( This customer should show up when either 90 days or 180 days filter is choicen)

~Customer H, purchased 11/1/2021, so this customer is in the 90 days bucket only. 

Customer	Product	Purchase Date	Active Customer?	90 Days Filter	180 Days Filter
A	1001	2/1/2022	Yes	No	No
B	1002	9/2/2021
C	1003	3/5/2022
D	1004	5/3/2021
E	1005	6/23/2021
F	1001	9/1/2021	No	Yes	Yes
G	1001	10/25/2021	No	Yes	Yes
H	1001	11/1/2021	No	Yes	No
I	1001	3/1/2022	Yes	No	No
J	1008	9/4/2018
K	1001	4/1/2020	No	Yes	Yes
L	1001	7/1/2019	No	Yes	Yes
M	1100	6/3/2020
N	1200	9/9/2021
O	1001	1/26/2022	No	Yes	No
P	1300	7/8/2021
Q	1001	11/1/2021	No	Yes	No
R	1111	11/1/2021
S	1001	9/13/2021	No	Yes	Yes
T	1001	4/24/2022	Yes	No	No

[amitchandak]

@leilei787 ,

With help from date table joined with purchase date

purchased in 90

Rolling 90 = CALCULATE(sum(Table[Customer]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-90,Day))

not purchased in 90

Not Rolling 90 = countx(values(Table[Customer]) , if(isblank([Rolling 90], [Customer], blank() ))

Rolling 180 = CALCULATE(sum(Table[Customer]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-180,Day))

not purchased in 90

Not Rolling 90 = countx(values(Table[Customer]) , if(isblank([Rolling 180 ], [Customer], blank() ))

[leilei787]

Hi Amitchandak, thank you!

With the solution you provide, it seems we can only setup individual filters instead of one filter with 3 options? ( or in your solution, looks like 4 options)

[v-rzhou-msft · Accepted solution]

Hi @leilei787 ,

Firstly, create a Filter table as below.

Filter = 
{"Active Customer?","90 Days Filter","180 Days Filter"}

Then create a measure.

Measure = 
VAR _SELECTION = SELECTEDVALUE('Filter'[Filter])
VAR _CURRENT_DAY = DATE(2022,04,28) /*TODAY()*/ 
VAR _90DAYS_BEFORE = _CURRENT_DAY - 90
VAR _180DAYS_BEFORE = _CURRENT_DAY - 180
RETURN
IF(
MAX('Table'[Product]) = 1001, 
SWITCH(
_SELECTION, 
"Active Customer?",IF(MAX('Table'[Purchase Date])>_90DAYS_BEFORE,"Yes","No"),
"90 Days Filter",IF(MAX('Table'[Purchase Date])<=_90DAYS_BEFORE,"Yes","No"),
"180 Days Filter",IF(MAX('Table'[Purchase Date])<=_180DAYS_BEFORE,"Yes","No")
)
)

Result is as below.

Best Regards,
Rico Zhou

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