How to find customers who haven't made purchase in last two years and other filters

[Dee]

Hi fam,

I have data that I would like to be able to create a filter that does the following,

1, Select Customer who haven't bought grapes in the last two years. 

2, Select Customer who bought grapes and something else or any fruit combinations only. eg 1001(grapes and melon)

3, Select Customers who haven't bought any fruits in the last year.

customerid      products       branch         transaction date

1001                grapes           AAA             1/1/17

1002                Oranges        AAA             2/1/17

1001                melon           AAA             3/1/17

1003                grapes           AAA             1/1/17

1004                citrus           VVV             1/1/17

1005               grapes           AAA             4/1/17

1003                grapes           AAA             1/1/18

1006                grapes           BBB             1/1/17

1007                mangoes       XNG             1/1/17

1008                grapes           AAA             1/1/19

1002                grapes           AAA             1/5/19

Any help towards achieving this will be highly appreciated.

TIA

[lc_finance · Accepted solution]

Hi @Dee ,

To have a general tutorial on analyzing customers in Power BI, you could a recent blog post I wrote on it:

https://finance-bi.com/power-bi-new-and-repeat-customers/

Regarding your specific questions, you can download my proposed solution from here.

Here are the steps:

1) Create a calendar table. It's always better to create a separate calendar table that has all possible dates. Here is the DAX formula for it:

Date = CALENDARAUTO()

2) Create a relationship between the calendar table and your sales table

3) Here are the DAX formulas that you asked about:

No grapes past 2 years = 
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())

The logic is similar for the three measures:

1) find the selected date (currentDate)

2) find the date 1 or 2 years ago, depending on the formula

3) create a range of the dates for the past year or 2 years

4) find the customer that bought the specific fruit or fruits

5) to find the customers that did not buy the specific fruit, show all customers except the ones who bought it (point 4 above)

Here is a screenshot:

Does this help you?

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[Dee]

RETURN IF(NOT datesInThePast2YearswithGrapes= BLANK() && NOT datesInThePast2YearswithSomethingElse= BLANK(), TRUE, BLANK())

 @lc_finance 

Thank you so much for your help, works perfectly.

how does that blank() function work in this context as featured?

[lc_finance]

Hi @Dee ,

I am very glad it works for you!

BLANK() returns an empty cell in Power BI. I used it so the rows with 'false' would be empty and disappear and Power BI would automatically filter out the false rows.

I am not sure why Power BI shows False even though the formula has BLANK().. 

That said, you can filter the False out in your visualization so this should exactly as you wish.

Let me know if you have any more questions

LC

[Dee]

Thanks once again @lc_finance , is there a way I can make it return a yes or no instead of the false filtering out.

[lc_finance]

Hi @Dee ,

sorry for the late reply.

Yes, there is a way to do it. Simply replace TRUE and BLANK() with "Yes" and "No".

Below is an example with No fruit pas year:

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(), "Yes", "No")

Does this help you?

Do not hesitate if you have more questions,

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com