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

[MKatsanevas]

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] ) ) ) )

[MKatsanevas · Accepted solution]

I actualy solved the issue. The calc is below. Thank you all for trying to help.

Lost customer =

VAR FirstDateSelected = FIRSTDATE( DATEADD ( 'Calendar'[calendar_date], 0, MONTH ) )

VAR LastDateSelected = LASTDATE( DATEADD ( 'Calendar'[calendar_date], 0, MONTH ) )

VAR CustomerLostDate = CALCULATE (

[LastPurchaseDate],

CALCULATETABLE (

Transactions,

FILTER (

ALL ( Partner ),

Partner[ParentPartnerID] = MAX ( Partner[ParentPartnerID] )

),

FILTER ( ALL ( 'Calendar' ), 'Calendar'[calendar_date] <= [LastPurchaseDate]) ) ) +365

RETURN

SUMX (

VALUES( Partner[ParentPartnerID] ),

IF (

CustomerLostDate>= FirstDateSelected

&& CustomerLostDate <= LastDateSelected, 1 ) )

[MKatsanevas]

Partner 

[MKatsanevas]

to be honest I was under the impression that measures can be in any table and they work the same is that not so?

[TheoC]

@MKatsanevas this is a Calculated Column, not a measure.  It won't work as a measure.

[MKatsanevas]

ok thank you for your help. I cant create a calcualted column. 

[MKatsanevas]

oh Im creating a mesure. Im usign a power BI model that I cant change so I cant add a calculated column

[TheoC]

@MKatsanevas also, try use the below Calculated Column for your Transaction table:

Customer Type = 

VAR _1 = CALCULATE ( LASTDATE ( 'Transactions'[PurchaseDate] ) , ALLEXCEPT ( 'Transactions' , Partner[Partner ID] ) , Transactions[PurchaseDate] )
VAR _2 = IF ( INT ( TODAY() - _1 ) > 365.2 , 1 , BLANK() )

RETURN

_2

[MKatsanevas]

this is my measure:

=

VAR _1=

CALCULATE (

LASTDATE ( Transactions[local_transaction_date] ) ,

FILTER (

ALLEXCEPT ('Transactions', Partner[ParentPartnerID]),

Transactions[local_transaction_date]))

VAR _2 = IF (INT( TODAY() - _1) > 365.2, 1, BLANK())

RETURN

_2

This is the result:

[TheoC]

Hi @MKatsanevas you can use this version as a measure:

Customer Type = 

VAR _1 = CALCULATE ( LASTDATE ( 'Transactions'[PurchaseDate] ) , ALLEXCEPT ( 'Transactions' , Partner[Partner ID] ) , Transactions[PurchaseDate] )
VAR _2 = IF ( INT ( TODAY() - _1 ) > 365.2 , 1 , BLANK() )

RETURN

_2

[MKatsanevas]

thank you but I get the same results a 1 in every column