Forum Discussion

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

DAX - Last year customer retention

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

 

 

 

 

DAX
Last Year
Previous Quarter
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

     

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

    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's avatar
      Diego_Vialle
      Icon for Helper II rankHelper II
      4 years ago

      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.