Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
=]
Solved! Go to 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
@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...
@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...
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |