Forum Discussion

Diego_Vialle's avatar
Diego_Vialle
Icon for Helper II rankHelper II
4 years ago
Solved

DAX - Customer retention

Calculate the sum of customers who bought and stopped buying in the last 12 months, and returned to buy in the quarter.   tamerj1  do you have any idea?
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Diego_Vialle 

    Please try the following code. To be honest I have no idea whether it works or not. However I tried to optimize it only by sense as I have no data to work with and measure the actual performance. This is a heavy code which in total requires a great number of context transitions and multiple iterations over the complete table. This iteration cannot be avoided otherwise there is no other way to gain access to each individual date which is required to calculate the period gap between each two consecutive transactions of the same customer. You might find it a bet complex but in fact it is simple and I hope it works. 

    Further you shall be able to use the same code to flag the customers who matches this criteria with the flexibility to filter different ranges of date (You can use a time slider based on dCalendar[Date] to select start and end dates of the selected period)

    Teste aula = 
SUMX (
    CALCULATETABLE ( 
        VALUES ( SBOPRODMS[Código do cliente] ), 
        SBOPRODMS[Documento] = "Nota fiscal de saída"
    ),
    VAR CurrentCustomerTable = 
        CALCULATETABLE ( SBOPRODMS, SBOPRODMS[Documento] = "Nota fiscal de saída" )
    VAR T1 = 
        ADDCOLUMNS ( 
            CurrentCustomerTable, 
            "Period", 
            VAR CurrentDate = 
                SBOPRODMS[Data Base]
            VAR PreviousDate = 
                MAXX ( FILTER ( CurrentCustomerTable, SBOPRODMS[Data Base] < CurrentDate ), SBOPRODMS[Data Base] )
            RETURN 
                IF ( NOT ISBLANK ( PreviousDate ), DATEDIFF ( PreviousDate, CurrentDate, DAY ) )
        )
    VAR MaxiDisengagementPeriod = 
        MAXX ( T1, [Period] )
    RETURN
        IF ( MaxiDisengagementPeriod > 365, 1 )
)


     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Diego_Vialle 

Please try the following code. To be honest I have no idea whether it works or not. However I tried to optimize it only by sense as I have no data to work with and measure the actual performance. This is a heavy code which in total requires a great number of context transitions and multiple iterations over the complete table. This iteration cannot be avoided otherwise there is no other way to gain access to each individual date which is required to calculate the period gap between each two consecutive transactions of the same customer. You might find it a bet complex but in fact it is simple and I hope it works. 

Further you shall be able to use the same code to flag the customers who matches this criteria with the flexibility to filter different ranges of date (You can use a time slider based on dCalendar[Date] to select start and end dates of the selected period)

Teste aula = 
SUMX (
    CALCULATETABLE ( 
        VALUES ( SBOPRODMS[Código do cliente] ), 
        SBOPRODMS[Documento] = "Nota fiscal de saída"
    ),
    VAR CurrentCustomerTable = 
        CALCULATETABLE ( SBOPRODMS, SBOPRODMS[Documento] = "Nota fiscal de saída" )
    VAR T1 = 
        ADDCOLUMNS ( 
            CurrentCustomerTable, 
            "Period", 
            VAR CurrentDate = 
                SBOPRODMS[Data Base]
            VAR PreviousDate = 
                MAXX ( FILTER ( CurrentCustomerTable, SBOPRODMS[Data Base] < CurrentDate ), SBOPRODMS[Data Base] )
            RETURN 
                IF ( NOT ISBLANK ( PreviousDate ), DATEDIFF ( PreviousDate, CurrentDate, DAY ) )
        )
    VAR MaxiDisengagementPeriod = 
        MAXX ( T1, [Period] )
    RETURN
        IF ( MaxiDisengagementPeriod > 365, 1 )
)


 

Diego_Vialle's avatar
Diego_Vialle
Icon for Helper II rankHelper II
4 years ago

Hello friend, I got the formula below in contact with a teacher. I will test yours because it seems smaller and more practical.

 

 

Reativacao Clientes = 
//Calcula os clientes do Intervalo 3
VAR vClientes_trimestre_filtro = 
CALCULATETABLE(
    VALUES(fVendas[Código do cliente]),
    fVendas[Documento] = "Nota fiscal de saída"
)
// Calcula a data início do intervalo 1
VAR vData_inicio = CALCULATE(MIN(fVendas[Data Base]), REMOVEFILTERS(dCalendario))

// Calcula a data máxima do Intervalo 1
VAR vData_fim = 
EOMONTH(
    CALCULATE(
        MAX(dCalendario[Date]),
        DATEADD(dCalendario[Date], -14, MONTH)
    ),
    -1
)
// Calcula os clientes do intervalo 1
VAR vClientes_intervalo_1 = 
CALCULATETABLE(
    VALUES(fVendas[Código do cliente]),
    fVendas[Documento] = "Nota fiscal de saída",
    FILTER(
        ALL(dCalendario[Date]),
        dCalendario[Date] >= vData_inicio &&
        dCalendario[Date] <= vData_fim
    )
)
// Calcula a data máxima do Intervalo 2
VAR vData_max_trimestre_anterior = 
CALCULATE(
    MAX(dCalendario[Date]),
    DATEADD(dCalendario[Date], -1, QUARTER)
)
// Calcula os clientes do intervalo 2
VAR vClientes_intervalo_2 = 
CALCULATETABLE(
    VALUES(fVendas[Código do cliente]),
    fVendas[Documento] = "Nota fiscal de saída",
    FILTER(
        ALL(dCalendario[Date]),
        dCalendario[Date] > vData_fim &&
        dCalendario[Date] <= vData_max_trimestre_anterior
    )
)
// Calcula os clientes que compraram no intervalo 3 e no intervalo 1
VAR vClientes_intervalo_3_e_1 = INTERSECT(vClientes_trimestre_filtro, vClientes_intervalo_1)

// Calculo os clientes que compraram no 3 e no 1, retirando os do intervalo 2
VAR vResultado = 
EXCEPT(vClientes_intervalo_3_e_1, vClientes_intervalo_2)

RETURN
COUNTROWS(vResultado)

 

support worksheet: