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.
Hello,
I am struggling with this problem of counting how many customers we lose every year.
Here you can see a simplified model of my data :
I tried some of the patterns explicited here : https://www.daxpatterns.com/new-and-returning-customers/#absolute-measures-for-new-/-lost-/-recovere....
Unfortunately, there is no LostDaysLimit in my case, the goal is only to calculate for each year these indicators :
So far, I've been able to calculate New, Reactivated and Loyal customers using measures with these steps :
Nb of sales per year = CALCULATE( COUNTA(Sales[Sales Amount]) + 0; FILTER( Sales; Sales[Customer ID] = EARLIER(Sales[Customer ID]) && YEAR(Sales[Sales Date]) = YEAR(EARLIER(Sales[Sales Date])) ) )
Previous Year Sales = CALCULATE( MIN(Sales[Nb of sales per year]) + 0; FILTER( Sales; Sales[Customer ID] = EARLIER(Sales[Customer ID]) && Sales[Sales Year] = EARLIER(Sales[Sales Year]) -1 ) )
Reactivated customers := CALCULATE( [Nb of customers]; FILTER( Sales; Sales[Nb of sales per year] > 0 && Sales[Previous Year Sales] = 0 && RELATED(Customer[Nb of purchases lifetime]) > Sales[Nb of sales per year] && Sales[Sales Year] <> RELATED(Customer[Year of first purchase]) ) ) Loyal customers := CALCULATE( [Nb of customers]; FILTER( Sales; Sales[Nb of sales per year] > 0 && Sales[Previous Year Sales] > 0 ) )
These measures give me the desired results :
But for the Nb of attrition measure, I'm really stuck on how to calculate it. So far, I've tried lots of solutions explicited on forums and blogs, but none of them gave me the desired results.
Nb of attrition := CALCULATE( [Nb of customers]; FILTER( Sales; Sales[Previous Year Sales] > 0 && Sales[Nb of sales per year] = 0 ) )
I know the code above is very naïve and I understand why it doesn't work. Because as long as a customer has not made any purchase on a specific year, the [Nb of sales per year] column would not even exist as there is no row for this customer in the Sales table.
The desired result should look like this :
Your help will be much appreciated.
Regards.
Solved! Go to Solution.
Hi @Anonymous
try these measure and let me know, I haven't tested them.
You may also want to switch off the row total on the matrix or you'd need to add a condition in the dax formula to not return anything on the totals
New Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), AND( CALCULATE( COUNTROWS( Sales ) ) > 0, CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] < MIN( 'Date'[Year] ) ) ) = 0 ) ) )
Reactivated Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 ) ) = 0 -- no purchases last year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] < MAX( 'Date'[Year] ) - 1 ) ) > 0 -- purchases before last year ) )
Loyal Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 ) ) > 0 -- purchases last year ) )
Attrition Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), CALCULATE( COUNTROWS( Sales ) ) = 0 -- no purchases this year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 ) ) > 0 -- purchases last year ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous
try these measure and let me know, I haven't tested them.
You may also want to switch off the row total on the matrix or you'd need to add a condition in the dax formula to not return anything on the totals
New Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), AND( CALCULATE( COUNTROWS( Sales ) ) > 0, CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] < MIN( 'Date'[Year] ) ) ) = 0 ) ) )
Reactivated Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 ) ) = 0 -- no purchases last year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] < MAX( 'Date'[Year] ) - 1 ) ) > 0 -- purchases before last year ) )
Loyal Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), CALCULATE( COUNTROWS( Sales ) ) > 0 -- purchases this year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 ) ) > 0 -- purchases last year ) )
Attrition Customers = CALCULATE( COUNTROWS( Customer ), FILTER( VALUES( Customer[Customer Code] ), CALCULATE( COUNTROWS( Sales ) ) = 0 -- no purchases this year && CALCULATE( COUNTROWS( Sales ), FILTER( ALL( 'Date'[Year] ), 'Date'[Year] = MAX( 'Date'[Year] ) - 1 ) ) > 0 -- purchases last year ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi, @LivioLanzo I have a similar setup and requirement, this solution works for me when I add it as a measure.
But the result the measure provides does not filter the customer Table. is it possible to have a similar solution as Custom Colum?
Kind Regards
@LivioLanzoMy bad, it works on my dummy data but not on real data.
The 3 others are good but the Attrition one shows no data.
I thought that maybe the solution might be to calculate a new table with one row for every customers and each year that will calculate their yearly number of sales. But I have no clue on how to achieve that.
Thanks for your help.
Hi @Anonymous
Here are some posts for you to check if they could help you. If it is not your case, please share your data file or more details so that we could help further on it.
https://community.powerbi.com/t5/Desktop/Lost-Customers-Issues-with-DAX-Calculation/m-p/288380
http://radacad.com/lost-customers-dax-calculation-for-power-bi
Regards,
Cherie
Hi @Anonymous
as far as I know it should also work on your real data if the model is the same. We would need to see which customers should be included but are not, but without access to the real data I can't figure that out.
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@LivioLanzoyou are right, it works. For other analysis purposes, I turned the bidirectional cross-filtering on between Customer and Sales. This stops the Attrition measure from working properly. But without it, it gives the correct results.
@LivioLanzoand @v-cherch-msft, thanks again for your help.
Regards.
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |