Forum Discussion

Veigar's avatar
Veigar
Frequent Visitor
5 years ago
Solved

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.

1 Reply