Forum Discussion

Diego_Vialle's avatar
Diego_Vialle
Helper 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 )
)


     

4 Replies

    • tamerj1's avatar
      tamerj1
      Community 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
        Helper 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:

         

         

         

         

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Diego_Vialle 

     

    Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.