Forum Discussion

chris_tappeiner's avatar
chris_tappeiner
New Member
2 years ago

Lost customers

Hi everybody!

I know, that the topic has been covered already in different postings, but somehow it doesn't work for me.

I use this code:

 

VAR var_Kunden_LJ =

FILTER(VALUES(Rechnungen[Bezeichnung neu]); NOT ISBLANK([_Umsatz]))   //list of clients with sales in the actual year

 

VAR var_Kunden_VJ =

FILTER(VALUES(Rechnungen[Bezeichnung neu]);
NOT ISBLANK(CALCULATE([_Umsatz];

FILTER(ALL(Kalender);Kalender[Geschäftsjahr_numerisch]<MAX(Kalender[Geschäftsjahr_numerisch]))))) 

//list of clients with sales in the last year

 

Return

CALCULATE([_Kfd. Kunden];

EXCEPT(var_Kunden_VJ;var_Kunden_LJ))    // calculate number of clients, taking only the clients from last year, that did not order this year.

 

If I switch the last two variables, I get the correct number of new clients, but the way it is now, I get only a void list.

I don't see, why it doesn't work.

I hope, the Infos are clear.

Looking desperatly for help ...

Christof

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    chris_tappeiner , for lost and new customer, we need two duration, based on that we can get new/lost customers

     

    example month based

     

    MTD = calculate([Sales],datesmtd('Date'[Date]))
    LMTD = calculate([Sales],DATESMTD(DATEADD('Date'[Date],-1,MONTH)))
    Lost Customer This Month = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([MTD]) && not(ISBLANK([LMTD])) , 1,BLANK()))
    New Customer This Month = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([LMTD]) && not(ISBLANK([MTD])) ,1,BLANK()))
    Retained Customer This Month = if(not(ISBLANK([MTD])) && not(ISBLANK([LMTD])) , 1,BLANK())

     

     

    Based on year

     

    This Year = calculate([Sales],datesytd('Date'[Date]))
    Last Year = calculate([Sales],DATESyTD(DATEADD('Date'[Date],-1,MONTH)))

    Lost Customer This Year = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([This Year]) && not(ISBLANK([Last Year])) , 1,BLANK()))
    New Customer This Year = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([Last Year]) && not(ISBLANK([This Year])) ,1,BLANK()))
    Retained Customer This Year = if(not(ISBLANK([This Year])) && not(ISBLANK([Last Year])) , 1,BLANK())

     

    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 5: LTD Vs Period Retention
    https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

    • chris_tappeiner's avatar
      chris_tappeiner
      New Member
      2 years ago

      Thanks for your answer!

      I use the result of my measure for a Pivot Table, creating a List of clients.

      Below my measure using english terms for easier understanding.

       

      This first part gives me the list of my actual clients of this year:

       

      VAR var_clients_actual =

      FILTER(VALUES(Customer[Customer Id]); NOT ISBLANK([_Sales]))    //_Sales is the measure to sum sales YtoD

       

      This part creates the list of customers which bought somewhen during the last fiscal year:

       

      VAR var_clients_lastYear =

      FILTER(VALUES(Customer[Customer Id]);
      NOT ISBLANK(CALCULATE([_Sales];

      FILTER(ALL(Calender);Calender[fiscalyear]<MAX(Calender[fiscalyear]))))) 

       

      And this is the last part, which I use to return the number of clients:

       

      Return

      CALCULATE(DISTINCTCOUNT(Customer[Customer Id]);    

      EXCEPT(var_clients_lastYear;var_clients_actual))    

       

      What drives me mad is that "EXCEPT(var_clients_actual;var_clients_actual;))"   

      works perfectly, generating a list of my new customers, but inverting the variables results in a void list. 

       

      Any clue, what I am missing here?

      Thanks a lot in advance!