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 a...
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.

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.