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.
I have a measure that calculates customer retention: customers who bought in the last 3 months. I need to bring the comparison of this measure from the previous year. I tried the formula below without success.
customer retention: customers who bought in the last 3 months.
Frequencia pedidos Trimestral =
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas,
fVendas[Data NF]
< TODAY () - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF]
< TODAY () ,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) )
comparison of this measure from the previous year:
Frequencia pedidos Trimestral PY =
VAR PreviousYearDate = DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas,
fVendas[Data NF]
< PreviousYearDate - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF]
< PreviousYearDate ,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
IF ( ISBLANK ( ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) ) ), 0, COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) )
Solved! Go to Solution.
Hi @Diego_Vialle
This should work. I see no problem except that this formula is not supposed to be sliced by date (which has no meaning). You can use it to flag customers or in a card visual. Here is the same formula with minor cozmatics
Frequencia pedidos Trimestral PY =
VAR PreviousYearDate =
DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
COALESCE ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ), 0 )
Hi @Diego_Vialle
This should work. I see no problem except that this formula is not supposed to be sliced by date (which has no meaning). You can use it to flag customers or in a card visual. Here is the same formula with minor cozmatics
Frequencia pedidos Trimestral PY =
VAR PreviousYearDate =
DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
COALESCE ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ), 0 )
You're right again, for this formula it doesn't make sense to have a date filter. I need another formula then for repeat customers.
@Diego_Vialle , You need measure like
rolling 3 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -3, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
rolling 3 last year =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max)-1, month(_max), Day(_max))
var _min = date(Year(_max), month(_max) -3, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
retained = countx(values(Customer[Customer]), if(not(isblank([rolling 3 last year])) && not(isblank([rolling 3])) , Customer[Customer], blank()))
lost = countx(values(Customer[Customer]), if(not(isblank([rolling 3 last year])) && (isblank([rolling 3])) , Customer[Customer], blank()))
new = countx(values(Customer[Customer]), if((isblank([rolling 3 last year])) && not(isblank([rolling 3])) , Customer[Customer], blank()))
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...
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |