Calcular clientes inativos

[LuanRamos]

Boa tarde, pessoal.

Tenho uma base com os seguintes campos: COD_CLIENTE, DATA_COMPRA, VLR_COMPRA.

Como posso calcular quantos clientes não fizeram compras nos últimos 6 meses?

Obrigado e abraços

=]

[AlB · Accepted solution]

Hi @LuanRamos 

It would help if you show some sample data and explain the requirements a bit more. What defines a purchase, is it a DATA_COMPRA? We'll assume that. Last six months since WHEN? Since today? We'll assume that. Where are all the customers defined? We'll assume in the fact table

Measure =
VAR minDate_ = EDATE (  TODAY (),-6 ) //Takes last 6 months as of today
VAR allClients_ = ALL ( Table1[COD_CLIENTE] )
VAR clientsWithPurchase_ =
    CALCULATETABLE ( DISTINCT ( Table1[COD_CLIENTE] ), Table1[DATA_COMPRA] > minDate_ )
VAR clientsNoPurchase_  = EXCEPT(allClients_, clientsWithPurchase_)  
RETURN
 COUNTROWS(clientsNoPurchase_)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak · Accepted solution]

@LuanRamos , Create measures like this and use them with COD_CLIENTE

Not in last 6 = if( isblank(CALCULATE(sum(Sales[VLR_COMPRA]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH)) ),1,0)
Not in last 6= if( isblank(CALCULATE(sum(Sales[VLR_COMPRA]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH)))  ,1,0)

Refer my blog on this Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[AlB · Accepted solution]

Hi @LuanRamos 

It would help if you show some sample data and explain the requirements a bit more. What defines a purchase, is it a DATA_COMPRA? We'll assume that. Last six months since WHEN? Since today? We'll assume that. Where are all the customers defined? We'll assume in the fact table

Measure =
VAR minDate_ = EDATE (  TODAY (),-6 ) //Takes last 6 months as of today
VAR allClients_ = ALL ( Table1[COD_CLIENTE] )
VAR clientsWithPurchase_ =
    CALCULATETABLE ( DISTINCT ( Table1[COD_CLIENTE] ), Table1[DATA_COMPRA] > minDate_ )
VAR clientsNoPurchase_  = EXCEPT(allClients_, clientsWithPurchase_)  
RETURN
 COUNTROWS(clientsNoPurchase_)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers