lost customers

[powerbiexpert22]

how to identify lost customer?

lost customers on given date are those customers who never placed order between past 90 days and future 90 days on given date

example lost customers on 01-June-2001 should be those customers who never placed orders between 01st march 2001 and 30-sept-2001

below is the sample data

[talespin · Accepted solution]

hi @powerbiexpert22 

I used Contoso Sales

This measure checks for Sales +/- 90 days from 2012-06-01, since I did not have recent data. Please change it to Today() or as per your need.

Sales Plus Minus 90 Days =

VAR _StartDate = DATE(2012,6,1) - 90

VAR _EndDate = DATE(2012,6,1) + 90

VAR _SalesCount = CALCULATE( COUNTROWS(Orders), 'Date'[Date] >= _StartDate && 'Date'[Date] <= _EndDate)

RETURN IF(_SalesCount = 0, "Lost Customer", BLANK())

[Anonymous · Accepted solution]

Hi @powerbiexpert22 

First of all, thanks to @talespin for your timely and effective response.

Here are my additions.

My slicer table:

You can create a measure as follows.

Measure = 
VAR _selectedvalue = SELECTEDVALUE('Date'[Date])
VAR _l90 = EOMONTH(_selectedvalue, -4) + 1
VAR _f90 = EOMONTH(_selectedvalue, +3)
VAR _cid = CALCULATE(MAX('order'[customerid]), FILTER('order', [orderdate] >= _l90 && [orderdate] <= _f90))
VAR _cname = CALCULATE(MAX(customer[name]), FILTER('customer', [customerid] = _cid))
RETURN
IF(MAX(customer[name]) = _cname, BLANK(), "lost customer")

Result:

If this is not the result you want, please show the result you want in excel format so we can help you better.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[talespin · Accepted solution]

hi @powerbiexpert22 

I used Contoso Sales

This measure checks for Sales +/- 90 days from 2012-06-01, since I did not have recent data. Please change it to Today() or as per your need.

Sales Plus Minus 90 Days =

VAR _StartDate = DATE(2012,6,1) - 90

VAR _EndDate = DATE(2012,6,1) + 90

VAR _SalesCount = CALCULATE( COUNTROWS(Orders), 'Date'[Date] >= _StartDate && 'Date'[Date] <= _EndDate)

RETURN IF(_SalesCount = 0, "Lost Customer", BLANK())