Forum Discussion

cnpdx's avatar
cnpdx
Icon for Helper II rankHelper II
9 years ago
Solved

How do I calculate Lost Customer Sales?

  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...
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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's avatar
    Vvelarde
    9 years ago

    cnpdx

     

    Try with this:

     

    IF

    (

       IF(SalesDetails<>12,

                IsBlank(LookupValue(....)),

                IsBlank(LookupValue(....))

    ),

    "Y",""

    )

     

  • cnpdx's avatar
    cnpdx
    9 years ago

    This is all you need:

     

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

     

     

cnpdx's avatar
cnpdx
Icon for Helper II rankHelper II
9 years ago

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's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

cnpdx

 

After Blank add a )

  • cnpdx's avatar
    cnpdx
    Icon for Helper II rankHelper II
    9 years ago

    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's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      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's avatar
        cnpdx
        Icon for Helper II rankHelper II
        9 years ago

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