topic Re: DAX - Last year customer retention in DAX Commands and Tips

DAX - Last year customer retention

Diego_Vialle — Thu, 23 Jun 2022 22:29:20 GMT

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 ) ) )

Re: DAX - Last year customer retention

amitchandak — Fri, 24 Jun 2022 03:31:49 GMT

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/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

Re: DAX - Last year customer retention

tamerj1 — Fri, 24 Jun 2022 04:14:15 GMT

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 )

Re: DAX - Last year customer retention

Diego_Vialle — Fri, 24 Jun 2022 12:51:31 GMT

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.