How do I calculate Lost Customer Sales?

[cnpdx]

I am trying to calcluate;

"what were sales last month of customers who didn't purchase this month?"

This article was amazing, and a real game changer to help calculate returning, new and lost customer counts. As well as sales of new and returning customers: 

New and Returning Customers

Can anyone modify or add to the above article to cacluate sales of customers last month who didn't purchase this month? using same syntax and variables as in the article. thank you so much for your help!!

[Vvelarde · Accepted solution]

@cnpdx

Lookvalue have a result which is not a boolean expression.

In your case, you have to add IF(lookvalue........) <> Blank() 

When the result is blank the formula give you a value and when is not another value ("Y" or "")

[Vvelarde · Accepted solution]

@cnpdx

Try with this:

IF

(

   IF(SalesDetails<>12,

            IsBlank(LookupValue(....)),

            IsBlank(LookupValue(....))

),

"Y",""

)

[cnpdx · Accepted solution]

This is all you need:

Lost customer sales = CALCULATE ( [SALES],PREVIOUSMONTH('DATE[DATE]),FILTER(CUSTOMER,[SALES]=0)  

[v-qiuyu-msft]

Hi @cnpdx,

In your scenario, you can create a calculated column like below:

PurchaseLastmonthNotthis month =
VAR CKey = Sales[CutomerKey]
return
IF(IF('Sales'[Month]<>12,
LOOKUPVALUE( Sales[CutomerKey], Sales[CutomerKey], CKey, Sales[Month], Sales[Month] + 1),LOOKUPVALUE( Sales[CutomerKey], Sales[CutomerKey], CKey, Sales[Month], 1,Sales[Year],Sales[Year]+1))=BLANK(),"Y","")

Best Regards,
Qiuyun Yu

[cnpdx]

Hi @v-qiuyu-msft,

Thanks for responding!

I totally get your logic and I think it will work for my problem, I'm just having a syntax issue. Can you take a look:

Thanks for your help!

[Vvelarde]

@cnpdx

After Blank add a )

[cnpdx]

Hi @Vvelarde

Thanks, adding the ) worked, but now I get this new error. does anyone have a solution for that (pic)? The error says it doesn't like the customer key field (text)...

[Vvelarde · Accepted solution]

@cnpdx

Lookvalue have a result which is not a boolean expression.

In your case, you have to add IF(lookvalue........) <> Blank() 

When the result is blank the formula give you a value and when is not another value ("Y" or "")

[cnpdx]

i'm just not clear where that IF(lookvalue........) <> Blank() fits in to the whole formula. can you elaborate?

[Vvelarde · Accepted solution]

@cnpdx

Try with this:

IF

(

   IF(SalesDetails<>12,

            IsBlank(LookupValue(....)),

            IsBlank(LookupValue(....))

),

"Y",""

)