Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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()
)
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
Hi
@giuliapiazza94
You might want to check this out
https://radacad.com/customer-retention-in-power-bi-dax-measures
BR
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |