Forum Discussion

v_franz's avatar
v_franz
Icon for Helper I rankHelper I
7 years ago
Solved

CALUCLATE Lost Clients

Hello,

 

how can I show "Lost" clients?

Lost clients are clients that bought last year, but didn't yet buy this year.

 

Measure Lost Clients = CALCULATE(
DISTINCTCOUNT(FACT_DE_VKDOK[KUNID]);
FACT_DE_VKDOK[SALES_VALUE])=0;
FACT_DE_VKDOK[SALES_VALUE_LAST_YEAR]<>0
)
my logic was:
Distinct Count all the Client IDs
where the sales value of this year  = 0 and the sales value of last year is <> 0.
 
I dont know why this doesn't work.
thank you helping
 
Viktor
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi v_franz ,

     

    Please make sure you didn’t miss your table name in Filter expression.  If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

    Measure for lost client = CALCULATE(DISTINCTCOUNT([Client ID]),FILTER(Table1,[Sales last year]<>0&&[Sales this year]=0))

    I modified the actual measure according to your data, please try agagin.

    Lost Clients = 
CALCULATE(DISTINCTCOUNT(FACT_DE_VKDOK[KUNID]);
FILTER(FACT_DE_VKDOK; FACT_DE_VKDOK[VKWERTNETTO]=0 
&& FACT_DE_VKDOK[VKWERTNETTO_VJ])<>0))

    Best regards,

    Dina Ye

8 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi v_franz 

    Please try this measure:

    Measure for lost client = CALCULATE(DISTINCTCOUNT([Client ID]),FILTER(Table1,[Sales last year]<>0&&[Sales this year]=0))

    Regards,

    Dina

    • v_franz's avatar
      v_franz
      Icon for Helper I rankHelper I
      7 years ago

      Hello Dina,

       

      thank you for your reply. But unfortunately it doesn't work.

      PowerBI tells me, Filter doesnt work for TRUE/FALSE statements.

      Where can there be the error?

       

      --> I tried it like this also:

      Lost Clients = CALCULATE(DISTINCTCOUNT(FACT_DE_VKDOK[KUNID]);FILTER(table1;sum(FACT_DE_VKDOK[VKWERTNETTO_VJ])<>0 && sum(FACT_DE_VKDOK[VKWERTNETTO])=0))
      But it tells me, Table1 is not a valid table, variable of functionname (sorry for the translation im Using PowerBI in German)

       

      Thank you

      Viktor