Forum Discussion
Lost customers - big dataset - optimizing performance. Help needed!
Hi all.
I am trying to calculate lost and returning customers on a large dataset. I have 4 years' worth of data (682 million rows), and approximately 2,4 million distinct customers.
I want to see how many customers that has purchased last "year" (365 days from "today" and a year before that), and not the last 365 days.
Most of the Dax formulas work fine, but it takes forever to get a result or i run out of memory. So how can I optimize a calculation like this without aggregating the dataset?
Anonymous , Try measures like
rolling 365 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) , Day(_max)-365 ),
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))rolling 365 before =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today()) -366
var _min = date(Year(_max), month(_max) , Day(_max)-365 ),
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))Lost Customer = countx(filter( Values(Customer[Customer]) , not(isblank([rolling 365 before])) && isblank([rolling 365])) ,[Customer])
refer
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458
2 Replies
- amitchandak
Super User
Anonymous , Try measures like
rolling 365 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) , Day(_max)-365 ),
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))rolling 365 before =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today()) -366
var _min = date(Year(_max), month(_max) , Day(_max)-365 ),
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))Lost Customer = countx(filter( Values(Customer[Customer]) , not(isblank([rolling 365 before])) && isblank([rolling 365])) ,[Customer])
refer
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458 Hi amitchandak and thanks for the reply!
I've tried your suggestion and it seems to work well. However, the lost customer count does not seem to be right based on other data I have.
Number of lost customers should be significantly higher. Ref. the below pictures. Any suggestions?
This are the results for the last 4 years:
This is a different view of the customer distribution the last 52 weeks: