Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dee
Helper III
Helper III

How to create a filter for customers who haven't made purchase in last two years

I am trying to create a filter that will allow me to

 

select a date range in which a customer hasn't made a purchase albeit two years, 6 months etc.(reading from the transaction date column)

 

select if the customer bought grapes and any other fruit or any combination in the last two years(last fruit buying date column)

 

select customers who haven't made any purchase in the last year.

 

Initially, I tried, the formulas as suggested by @lc_finance  below but they end up as columns whilst I am trying to create filters that would allow me to select product and range as required.

var currentDate = LASTDATE('Date'[Date])
var past2Years = DATESBETWEEN('Date'[Date],DATEADD(currentDate,-2,YEAR),currentDate)
var datesInThePast2YearswithGrapes = CALCULATE(COUNTROWS('Sales table'), 'Date'[Date] IN past2Years , 'Sales table'[products] = "grapes")
RETURN IF(datesInThePast2YearswithGrapes= BLANK(), TRUE, BLANK())


Grapes and something else = 
var currentDate = LASTDATE('Date'[Date])
var past2Years = DATESBETWEEN('Date'[Date],DATEADD(currentDate,-2,YEAR),currentDate)
var datesInThePast2YearswithGrapes = CALCULATE(COUNTROWS('Sales table'), 'Date'[Date] IN past2Years , 'Sales table'[products] = "grapes")
var datesInThePast2YearswithSomethingElse = CALCULATE(COUNTROWS('Sales table'), 'Date'[Date] IN past2Years , NOT 'Sales table'[products] = "grapes")
RETURN IF(NOT datesInThePast2YearswithGrapes= BLANK() && NOT datesInThePast2YearswithSomethingElse= BLANK(), TRUE, BLANK())

No fruit past year = 
var currentDate = LASTDATE('Date'[Date])
var past2Years = DATESBETWEEN('Date'[Date],DATEADD(currentDate,-1,YEAR),currentDate)
var datesInThePast2YearswithFruits = CALCULATE(COUNTROWS('Sales table'), 'Date'[Date] IN past2Years)
RETURN IF(datesInThePast2YearswithFruits= BLANK(), TRUE, BLANK())```

below is my sample data

customerid products transactiondate lastfruitbuyingdate
1001    grapes      1/1/2017        1/1/2017
1002    Oranges     2/1/2017        1/1/2017
1001    melon       3/1/2017        1/1/2017
1003    grapes      1/1/2017        2/3/2018
1004    citrus      1/1/2017        1/4/2017
1005    grapes      4/1/2017        2/4/2017
1003    grapes      1/1/2018        5/4/2018
1006    grapes      1/1/2017       6/7/2018
1007    mangoes     1/1/2017       7/7/2018
1008    grapes      1/1/2019       7/8/2019
1002    grapes      1/5/2019       7/9/2019

Any help would be highly apprciated.

 

1 ACCEPTED SOLUTION

Hi @Dee 

Leave two tables no relationship

Capture12.JPG

Create measures

min selected = MIN('date table'[Date])

max each id = CALCULATE(MAX('Table'[transactiondate]),ALLEXCEPT('Table','Table'[customerid]))

con1 = CALCULATE(DISTINCTCOUNT('Table'[customerid]),FILTER('Table',[max each id]<[min selected]))

con 2 = CALCULATE(DISTINCTCOUNT('Table'[customerid]),FILTER('Table','Table'[lastfruitbuyingdate]>=[min selected]&&'Table'[lastfruitbuyingdate]<=TODAY()))

con3 = CALCULATE(DISTINCTCOUNT('Table'[customerid]),FILTER('Table',[max each id]<[min selected]))

result 1

Capture13.JPG

result 2

Capture14.JPG

result 3

Capture15.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Dee 

Based on my understanding, 

1. select a date range in which a customer hasn't made a purchase albeit two years, 6 months etc.(reading from the transaction date column)

eg, select a date period from slicer: last 2 years and 6 months(2017/5~2019/11),

     we need to find the customers who' transactiondate aren't in this period (transactiondate<2017/5).

 

2. select if the customer bought grapes and any other fruit or any combination in the last two years(last fruit buying date column)

eg, select "grapes" from "product" slicer, 

      find the customers who bought grapes  last fruit buying date column are in the last two years(2017/11~2019/11). 

      (it is to say the last fruit buying date of customers who bought grapes is >=2017/11 and <=today)

 

3. select customers who haven't made any purchase in the last year.

all the transactiondate for one customer aren't in the period of 2018/11~2019/11.

 

Is my understanding correct?

 

Best Regards
Maggie

Yes, but now I want it in a filter dropdown to allow me to select the options based on the data.

Hi @Dee 

Leave two tables no relationship

Capture12.JPG

Create measures

min selected = MIN('date table'[Date])

max each id = CALCULATE(MAX('Table'[transactiondate]),ALLEXCEPT('Table','Table'[customerid]))

con1 = CALCULATE(DISTINCTCOUNT('Table'[customerid]),FILTER('Table',[max each id]<[min selected]))

con 2 = CALCULATE(DISTINCTCOUNT('Table'[customerid]),FILTER('Table','Table'[lastfruitbuyingdate]>=[min selected]&&'Table'[lastfruitbuyingdate]<=TODAY()))

con3 = CALCULATE(DISTINCTCOUNT('Table'[customerid]),FILTER('Table',[max each id]<[min selected]))

result 1

Capture13.JPG

result 2

Capture14.JPG

result 3

Capture15.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.