Customers found / lost measure

[giuliapiazza94]

Hi guys,

i need to create a measure or field to identify which customers have bought this year but not last year (found customer) and which customers bought last year and not this year (lost customer).

This measure/field has to be a dynamic measure/field, so if I choose "2021" in filter object I'd like to see found customers and lost customers in 2021. The same if i choose 2022, etc.

I've tried to create a measure but if I do a drill down it doesn't work (page 2), so i tried to create a field and it works but i had to create a field for each time coparison (page 1) and I don't like it.

Can you help me?

Thank you all 😁

file pbix

https://drive.google.com/file/d/1_Uoaz_WTofGT7YUBdMnxbpupp-odWO3R/view?usp=sharing

[Greg_Deckler]

@giuliapiazza94 Try this:

Measure = 
    VAR __Customer = MAX('Customer'[K_CodCli])
    VAR __Year = MAX('Calendar'[Year])
    VAR __Table = SUMMARIZE(FILTER(ALL('Sales'),YEAR('Sales'[K_DataRegistraz]) = __Year - 1),[K_CodCli],"Sales",SUM('Sales'[Importo totale]))
    VAR __Table1 = SUMMARIZE(FILTER(ALL('Sales'),YEAR([K_DataRegistraz]) = __Year),[K_CodCli],"Sales",SUM('Sales'[Importo totale]))
    VAR __LastYearCust = 
        SELECTCOLUMNS(
            FILTER(__Table,[Sales] > 0 && [Sales] <> BLANK()),
            "ID",[K_CodCli]
        )
    VAR __ThisYearCust = 
        SELECTCOLUMNS(
            FILTER(__Table1,[Sales] > 0 && [Sales] <> BLANK()),
            "ID",[K_CodCli]
        )
RETURN
    SWITCH(TRUE(),
        __Customer IN __ThisYearCust && NOT(__Customer IN __LastYearCust),"Found",
        __Customer IN __LastYearCust && NOT(__Customer IN __ThisYearCust),"Lost",
        BLANK()
    )

[giuliapiazza94]

If I use drill down It doesn't work, like my measure.

A matrix, in the rows there is customer and item

Customer 10007 - CONDOMINIO PITAGORA has bought 100.000€.

If i use drill down it shows all item, while i'd like to see only items that he has bought

[Anonymous]

Hi 
@giuliapiazza94  

You might want to check this out

https://radacad.com/customer-retention-in-power-bi-dax-measures


BR