Forum Discussion
Lost customer
Hi everyone,
I have four tables:
SALES(S_ID, S_Date, S_Customer, ...)
CALENDAR(Date, ...),
MOVEMENTS(M_ID, M_causal, M_Date, ...)
CUSTOMERS(C_ID, ...)
I would like to get the lost customers.
Let me explain with an example:
Customer 001 has ordered in 2018 and then no more. I would like to count all of customers that have ordered in a certain year an then no more I wrote this measure:
Lost :=
VAR _MAX = YEAR(MAX(CALENDAR[Date]))
VAR T =
SUMX(
FILTER(
SUMMARIZE(
ALL(SALES),
S_Customer,
"Last operation", [Last operation per client],
"_count", 1
),
YEAR([Last operation]) = _MAX
),
[_count]
)
RETURN T
And:
Last operation per client :=
MAX(
MAXX(FILTER(MOVEMENTS, MOVEMENTS[M_Causal] = "Returned"), MOVEMENTS[M_Date]),
MAX(SALES[S_Date])
)
I have datas for (2018, 2019, 2020, 2021)
It works if i put a filter by year, but when i remove the filter it shows me the 2021 result.
I want to calculate the Average of lost customers if there is no filter.
I tried to calculate in the first measure also:
VAR _MIN = YEAR(MIN(CALENDAR[Date]))
And then if _MAX and _MIN where equal, it meant that there is a filter on the page.
So if not, i want to calculate the average of lost customer per year.
Veigar , I have two blogs on that, based on the duration you can work on lost customers
refer if these can help
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 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458You can use datesytd for year
1 Reply
- amitchandakSuper User
Veigar , I have two blogs on that, based on the duration you can work on lost customers
refer if these can help
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 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458You can use datesytd for year