Forum Discussion
Lost customers - big dataset - optimizing performance. Help needed!
- 4 years ago
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
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