Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
26 | |
23 | |
15 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |