Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello DAX experts,
I cannot manage with one case. I need to calculate number of lost customers every month. The hardest thing for me is that it must be KPI dynamic report, so i need to show the changes between months. How many customers we lost every month per year. I quess that i need to use a measure.
Here is my sample dataset:
# | Type | Doc No. | Post Date | BP Code | Net Amount | Country |
1 | A/R Invoice | 2001000001 | 07/01/2020 | C00483 | 910.89 | PL |
2 | A/R Invoice | 2001000002 | 07/01/2020 | C00584 | 2754 | PL |
3 | A/R Invoice | 2001000003 | 09/01/2020 | C00021 | 499.23 | PL |
4 | A/R Invoice | 2001000004 | 09/01/2020 | C00045 | 1044 | PL |
5 | A/R Invoice | 2001000005 | 09/01/2020 | C00044 | 2144.5 | PL |
6 | A/R Invoice | 2001000006 | 09/01/2020 | C00636 | 36.57 | PL |
7 | A/R Invoice | 2001000007 | 09/01/2020 | C00524 | 78.02 | PL |
8 | A/R Invoice | 2001000008 | 09/01/2020 | C00637 | 34.13 | PL |
9 | A/R Invoice | 2001000009 | 09/01/2020 | C00638 | 126.72 | PL |
10 | A/R Invoice | 2001000001 | 11/03/2020 | C00483 | 910.89 | PL |
11 | A/R Invoice | 2001000002 | 11/03/2020 | C00584 | 2754 | PL |
12 | A/R Invoice | 2001000003 | 11/03/2020 | C00021 | 499.23 | PL |
13 | A/R Invoice | 2001000004 | 11/03/2020 | C00045 | 1044 | PL |
14 | A/R Invoice | 2001000005 | 11/03/2020 | C00044 | 2144.5 | PL |
15 | A/R Invoice | 2001000006 | 11/03/2020 | C00636 | 36.57 | PL |
16 | A/R Invoice | 2001000007 | 11/03/2020 | C00524 | 78.02 | PL |
17 | A/R Invoice | 2001000008 | 11/03/2020 | C00637 | 34.13 | PL |
18 | A/R Invoice | 2001000009 | 11/03/2020 | C00638 | 126.72 | PL |
19 | A/R Invoice | 2001000019 | 11/03/2020 | C00042 | 1077.43 | PL |
20 | A/R Invoice | 2001000020 | 11/03/2020 | C00153 | 1232.91 | PL |
The output table should looks like below:
Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
2020 | ||||||||||||
Lost Customer | 12 | 54 | 65 | 76 | 44 | |||||||
New Customer | ||||||||||||
Loyal Customer | ||||||||||||
Returned Customer | ||||||||||||
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
2019 | ||||||||||||
Lost Customer | 12 | 54 | 65 | 76 | 44 | 76 | 12 | 54 | 65 | 76 | 44 | 76 |
New Customer | ||||||||||||
Loyal Customer | ||||||||||||
Returned Customer |
Now i have created the calculated column like below, but it shows only the proper result up to date:
Before i will prepare the correct one measure i have tried to display the last transaction date for each BP code like below. Unfortuately it shows only the last tranasaction date within particular month (not in all data range before each EndOfMonth date).
remarks:
"Data ksiegowania" means Post date.
The lost customer doesnt buy anything for at least 30 days.
Thank you in advance.
Solved! Go to Solution.
Hi,
You may download my PBI file from here. Based on the data that you have shared, the result should be 9 for Feb 2020.
Hope this helps.
Hi,
Why are there 2 rows for Lost Customers for 2020? Also, does 12 mean that you lost 12 customers in January 2020 (as compated to December 2019)?
Hi,
You may download my PBI file from here. Based on the data that you have shared, the result should be 9 for Feb 2020.
Hope this helps.
Thank you @Ashish_Mathur for your help and time. That is the best solution which i get as far.
Measure= COUNTROWS(FILTER(SUMMARIZE(ALL(Data[BP Code]),Data[BP Code],"ABCD",[Amount],"EFGH",[Amount in previous month]),[ABCD]=0&&[Amount in previous month]>0))
My thoughts regarding this solution:
We don'nt need to calculate how many days we have without any purchase for each customer. What we need to say that they don't buy at least 30 days is to compare two neighbours months. We need to remember that it considers only two months data range. It does'nt count lost customers from couple months ago up to date. For the second in the begiging of the current month we will have the highest number of lost customers because of zero total amount in the current month.
You are welcome.
Hi,
Share a simple dataset and show the expected result.
@Anonymous - I have a lost customer's measure in my DAX Cookbook. If you could provide some basic information could probably adapt that to your situation. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |