Forum Discussion

leilei787's avatar
leilei787
Helper II
4 years ago
Solved

Looking for a filter solution

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. 

 

CustomerProductPurchase DateActive Customer?90 Days Filter180 Days Filter
A10012/1/2022YesNoNo
B10029/2/2021   
C10033/5/2022   
D10045/3/2021   
E10056/23/2021   
F10019/1/2021NoYesYes
G100110/25/2021NoYesYes
H100111/1/2021NoYesNo
I10013/1/2022YesNoNo
J10089/4/2018   
K10014/1/2020NoYesYes
L10017/1/2019NoYesYes
M11006/3/2020   
N12009/9/2021   
O10011/26/2022NoYesNo
P13007/8/2021   
Q100111/1/2021NoYesNo
R111111/1/2021   
S10019/13/2021NoYesYes
T10014/24/2022YesNoNo
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      leilei787
      Helper II
      4 years ago

      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)

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.