Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

lost customers

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-200...
Need Help
  • talespin's avatar
    talespin
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.