Trying to create Lost Customers measure and getting out of memory error.

[Anonymous]

Hello . I was hoping someone could help me. I created the calc below for lost customers. It works great however when the range is expanded I get a memory error. this query uses more memory than is configured. Code is below. I have tried to add variables to it to optimize it but cant seem to get it to work. Any help or sugestions would be highly appreciated.

Lost UBP =

IF (

NOT (

MIN ( 'Calendar'[calendar_date] )

> CALCULATE ( MAX (Transactions[local_transaction_date] ), ALL ( Transactions) ) ),

COUNTROWS (

FILTER (

ADDCOLUMNS (

FILTER (

CALCULATETABLE (

ADDCOLUMNS (

CALCULATETABLE (

VALUES ( Partner[ParentPartnerID] ), Transactions),

"CustomerLostDate", [LastPurchaseDate]+ 365),

FILTER (

ALL ( 'Calendar' ),

AND (

'Calendar'[calendar_date] < MIN ( 'Calendar'[calendar_date] ),

'Calendar'[calendar_date] >= MIN ( 'Calendar'[calendar_date] )- 365))),

AND (

AND (

[CustomerLostDate] >= MIN ( 'Calendar'[calendar_date] ),

[CustomerLostDate] <= MAX ( 'Calendar'[calendar_date] )),

[CustomerLostDate] <=

CALCULATE ( MAX ( Transactions[local_transaction_date] ), ALL ( Transactions ) ) ) ),

"FirstBuyInPeriod", [FirstPurchaseDate]),

OR (

ISBLANK ( [FirstBuyInPeriod] ),

[FirstBuyInPeriod] > [CustomerLostDate] ) ) ) )

[TheoC]

@Anonymous okay, to remove all dates other than the specific and provide a 1 for Lost Customer, here is the measure:

mCustomer Type = 

VAR _1 = CALCULATE ( LASTDATE ( 'Transaction'[Purchase Date] ) , ALLEXCEPT ( 'Transaction' , Partner[PartnerID] ) , Transaction[Purchase Date] )
VAR _2 = IF ( INT ( TODAY() - _1 ) > 365.2 , 1 , BLANK() )

RETURN

MAXX ( FILTER ( 'Transaction' , 'Transaction'[Purchase Date] = _1 ) , _2 )

Hope this is it 🙂

Theo

[Anonymous]

Nope. here is my calc below. the top table is the one with your new calc. The one below is the accuare one.

[sevenhills]

Check this article about Lost customers DAX 

https://www.daxpatterns.com/new-and-returning-customers/

You can avoid nested calculate tables ...

[TheoC]

Hi @Anonymous 

Can you provide the Table structures you're dealing with and some context to what fields you use to determine what a "Lost Customer" is?

Thanks in advance.

Theo

[Anonymous]

Hi Theo the first purchase date is the min date where sales >0 same with last purchase date it's the max date where sales >0. We have the customer in the partner table the sales are in the transactions table and we have the calendar. We have sales that go back 10 years. A lost customer is a parent partner id that purchasesd in the past but not in the last 12 months. Any help would be appreciated 

[Ashish_Mathur]

Hi,

Write these measures:

Date of last sale = calculate(max(Data[sales date]),datesbetween(calendar[date],minx(all(calendar[date]),calendar[date]),max(calendar[date])))

Lost customers = countrows(filter(calculatetable(values(Partner[ParentPartnerID]),datesbetween(calendar[date],edate(min(calendar[date]),-11),max(calendar[date]))),[date of last sale]<edate(min(calendar[date]),-11)))

To your matrix visual, drag Year and Month from the Calendar Table and ParentPartnerID from the Partner table.

If this does not help, then share the link from where i can download your PBI file. 

[Anonymous]

LOST UBP3 =

COUNTROWS (

FILTER (

CALCULATETABLE (

VALUES ( Partner[ParentPartnerID] ),

DATESBETWEEN (

'Calendar'[calendar_date],

EDATE ( MIN ( 'Calendar'[calendar_date],), -11 ),

MAX ( 'Calendar'[calendar_date],)

)

),

[LastPurchaseDate] < EDATE ( MIN ( 'Calendar'[calendar_date] ), -11 )

)

)

Im geting this error. thanks for your help

[Anonymous]

same thing Im geting 1's across all months. It needs to only show once in the month 12 months after last purchase date

[Ashish_Mathur]

Remove the extra comma in the MIN() function.

[TheoC]

@Anonymous okay, that makes a lot more sense.  So, theoretically speaking, the logic is as follows:

IF the MAX Purchase Date of a specific Partner ID is greater than 12 months old then flag the Partner ID as a Lost Customer otherwise ignore

Is the above correct?

[Anonymous]

 Correct I want a 1 in the month 12 months after last purchase as that is the last month/date 

[TheoC]

@Anonymous 

I'm not entirely sure what you mean by the 1 in the 12 months, sorry!  But hopefully the following helps.   Add a calculated column to your Partner table and use the following (adjust to your table and column names please):

Customer Type = 

VAR _1 = CALCULATE ( LASTDATE ( 'Transactions'[Purchase Date] ) , FILTER ( ALL ( 'Transactions' ) , Transaction[Partner ID] = Partner[Partner ID] ) )
VAR _2 = IF ( INT ( TODAY() - _1 ) > 365.25 , "Lost Customer" , "Active" )

RETURN

_2

Output of the above has been tested and runs like below - let me know how it goes k!

Theo 🙂

[Anonymous]

Also keep in mind that these fields are text not numbers

Transactions[ParentPartnerID]=MIN(Partner[ParentPartnerID]) 

[TheoC]

@Anonymous the format of the fields does not affect the columns.  It's just that the formula I put together was adjusted and created the error.

[Anonymous]

Im geting an error

[TheoC]

Hi @Anonymous it's not reading the VAR _2 properly which implies that VAR _1 isn't closed off correctly. You may need another bracket at the end of VAR _1?

[TheoC]

@Anonymous you aren't using the formula I provided. There is a "MAX" in your VAR _1 which is not the case in mine.

Please use:

Customer Type = 

VAR _1 = CALCULATE ( LASTDATE ( 'Transactions'[Purchase Date] ) , FILTER ( ALL ( 'Transactions' ) , Transaction[Partner ID] = Partner[Partner ID] ) )
VAR _2 = IF ( INT ( TODAY() - _1 ) > 365.25 , "Lost Customer" , "Active" )

RETURN

_2

[Anonymous]

The problem is I get the lost customer in every month. I only want a 1 in the month 12 months after so I can count all my lost customers. Like this:

[Anonymous]

I get an eror if I dont use the max since its a text column and not a number

[TheoC]

Hi @Anonymous 

Did you create the Calculated Column in the Partner table or the Transaction table?  I put the formula together for the Partner table prior to realising what you wanted with the "1" in the month.