Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Impactful 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-2001 should be those customers who never placed orders between 01st march 2001 and 30-sept-2001

 

below is the sample data

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

2 Replies

  • talespin's avatar
    talespin
    Solution Sage
    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
    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.