Lost customers - big dataset - optimizing performance. Help needed!

[Anonymous]

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?

Lost customers 365 days =

VAR customerpurchased = 

     CALCULATETABLE (

          VALUES('v_AggDayGameChannelPersonTotal2018-2022'[%PersonPrn_KEY]),

          FILTER(ALL(v_DimDate), v_DimDate[Dato] > MAX( v_DimDate[Dato]) - 730

              && v_DimDate[Dato] < MAX( v_DimDate[Dato]) - 366))

VAR PriorCustomers =

     CALCULATETABLE (

          VALUES('v_AggDayGameChannelPersonTotal2018-2022'[%PersonPrn_KEY]),

          FILTER(ALL(v_DimDate), v_DimDate[Dato] > MAX( v_DimDate[Dato]) - 365

               && v_DimDate[Dato] < MAX( v_DimDate[Dato] ) ) )

RETURN

COUNTROWS( EXCEPT( customerpurchased, PriorCustomers ) ) * -1

Any help is greatly appreciated!

AggDayGame... is structured like this: 

The model is structured like this: 

[Anonymous]

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: 

[amitchandak · Accepted solution]

@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