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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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