HOW MANY CUSTOMERS WE'VE LOST THIS YEAR?

[gabrielefugazzi]

I need your help to answer the question highlighted above.

WHAT THE DATA LOOKS LIKE (Tabular Model, Dynamic Live Connection)

- Fact table: SELLINGS (Product, Customer, Amount, Price, …)

- Dimension Table: CUSTOMERS (id, name, …)

DESIDERED CUSTOMERS SEGMENTATION

LABEL	IDENTIFIES
-100	Lost customer: the year before was the last time he bought
-1	Descreased customer: the year before he bought more than now
1	Increased customer: the year before he bought less than now
100	Gained customer: this year was the first time he has bought

WHAT I’VE DONE SO FAR

I’ve created a measure as follows to calculated the segmentation for each customers (this formula gives the desidered output):

segmentation =

VAR maxyear = max(SELLINGS[year])

var sellings = round(CALCULATE(sum(SELLINGS[price]), SELLINGS[year] = maxyear), 2)

var sellingsBefore = round(CALCULATE(sum(SELLINGS[price]), SELLINGS[year] = maxyear -1), 2)

return if(and(sellingsBefore <> 0, sellings <= 0), -100, if(and(sellingsBefore = 0, f sellings > 0), 100, if(sellings > sellingsBefore, 1, if(sellings < sellingsBefore, -1, 0))))

And tried the following formula to calculate the lost customers:

lostCustomers = CALCULATE(DISTINCTCOUNT(CUSTOMERS[id]), FILTER ( SELLINGS, SELLINGS[segmentation] = -100))

The value returned is blank but I know the lost customer are 37.

WHAT I GUESS

I guess my formula is wrong because the formula is not applied to the customer id before the distinct count is applied. The problem is I don’t know how to fix it. How to build a matrix style table on which I can perform the distinct count?

CAN YOU HELP ME?

How can I count customers and show in a graph how many for each segmentation? Thank you!

[tex628]

Try this measure, if it works for lost customers we can easily make one for each segmentation:

Lost Customers =
VAR Year = MAX(Calendar[Year])
VAR Year-1 = Year -1 
VAR Year-2 = Year -2 
Return
CALCULATE(
COUNTROWS(
SUMMARIZE( 
Customers[ID],
"Year" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year) ,
"Year-1" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-1) ,
"Year-2" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-2)
)),
[Year] = BLANK(),
[Year-1] = BLANK(),
[Year-2] <> BLANK()
)

Br,
J

[gabrielefugazzi]

Hi Johannes,

I've applied your formula and noted this errors highlightened (please look at the italian tables and fields names which I've translated before for a better understanding in the community... fatturato = SELLINGS, codice_cliente_statistico = customer id)

Since an error is detected on the filters applied to the summarize function I've tried the following:

Lost Customers =
VAR Year = MAX(fatturato[anno_fattura])

Return
CALCULATE(
COUNTROWS( FILTER (
SUMMARIZE(
fatturato, fatturato[codice_cliente_statistico],
"Year" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year) ,
"Year-1" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year-1)),

[Year] = BLANK() &&
[Year-1] <> BLANK() )

))

But the result returned is still BLANK 🙄.

Thank for your help

[tex628]

There is a ")" too much here:

[gabrielefugazzi]

Removing it produces the following error (always on the filter clause)

[tex628]

My badd, wasn't thinking straight! You should be totally correct with the filter() approach which concerns me as to why you're getting a blank result there. If you just do a normal countrows without any filters, what result are you getting? 

Lost Customers =
VAR Year = MAX(Calendar[Year])
Return
COUNTROWS(
SUMMARIZE( 
Customers[ID],
"Year" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year) ,
"Year-1" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-1) ,
"Year-2" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-2)
)

[gabrielefugazzi]

Not problem at all! I'm just learning

Doing as suggested I've defined the formula as follows:

Lost Customers =

VAR Year = MAX(fatturato[anno_fattura])

Return

CALCULATE(

COUNTROWS(

SUMMARIZE(

fatturato, fatturato[codice_cliente_statistico],

"Year" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year) ,

"Year-1" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year-1))

))

From this formula the output should be the total number of customers with their total sale amount per year (if I'm correct). The output is 10826.

Kind of strange result because running the query in excel (connected to the tabular model) the result is 5791 customers in 2019 and 2020 (same period selected in PowerBI).

I've also made an attempt to change COUNTROWS with DISTINCTCOUNT but wasn't successfull because that combination of function is refused by DAX (Parameter is not of the correct type).

I'm wrong at something but cannot see it.

[tex628]

With 2020 selected in the slicer. This should return the exact number of distinct customers with sales in either 2020 or 2019. 

If you add this in a table with your customer column you should get the sales for 2020. Might make it easier to compare and see if things are being calculated correctly:

2020 = 
SUMX(
SUMMARIZE(
fatturato, fatturato[codice_cliente_statistico],
"Year" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year) ,
"Year-1" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year-1)), 
[Year]
)

Br,
J

[gabrielefugazzi · Accepted solution]

Playing with the formula you provided and changing it I've came to the desidered solution:

Lost Customers =

VAR Year = MAX(fatturato[anno_fattura])

Return

CALCULATE(

COUNTROWS( FILTER (

SUMMARIZE(

fatturato, fatturato[codice_cliente_statistico],

"Year" , IF(ISBLANK(CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year)), 0, CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year)) ,

"Year-1" , IF(ISBLANK(CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year-1)), 0, CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year-1))) ,

[Year] <= 0 &&

[Year-1] > 0 )

))

Thank you very much!

[tex628]

Wonderful to hear! Good look with future calculations 🙂 

Br,
J

[amitchandak]

@gabrielefugazzi , refer this blog. And use Datesytd in place of datesmtd

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Also refer: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...