DAX - Last year customer retention

[Diego_Vialle]

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

[tamerj1 · Accepted 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 )

[Diego_Vialle]

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.

[amitchandak]

@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...