Forum Discussion

manar_alamri's avatar
manar_alamri
Icon for Helper I rankHelper I
1 year ago

Customers Retention by quarters

 

Hello everyone ,

I want to do two things in power bi : first , calculate the customer retention by quarters , years and centers( Customers who returned from the previous quarter to the same center) ex [jan 2023, feb 2023, mar 2023 will look from Oct 2022 , Nov 2022, Dec 2022] I do not want to count the same customer more than one in the same quarter.

 

Second, calculate the new customers in each quarter .

I have only one table (customers) with date and guest code columns 

 

 

I did a small sample here  

 

How I can do like this in power bi ?

I will be happy if you can help me.

Need Help

10 Replies

  • lucadelicio's avatar
    lucadelicio
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    Hi manar_alamri 
    try this measure:

    New Clients =
    CALCULATE(
        DISTINCTCOUNT(Table1[guest code]),
        FILTER(
            Table1,
            Table1[date] =
                CALCULATE(MIN(Table1[date]),
                    ALLEXCEPT(Table1, Table1[guest code])
                )
        )
    )
    put in a table with the period column that you need.


  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    manar_alamri , Try using measures for all 3 and create a separate date table for this

     

    PreviousQuarterCustomers =
    CALCULATE(
    DISTINCTCOUNT(customers[guest_code]),
    DATEADD(customers[date], -1, QUARTER)
    )

     

     

    CurrentQuarterCustomers =
    DISTINCTCOUNT(customers[guest_code])

     

     

    RetainedCustomers =
    CALCULATE(
    DISTINCTCOUNT(customers[guest_code]),
    FILTER(
    customers,
    customers[guest_code] IN
    CALCULATETABLE(
    VALUES(customers[guest_code]),
    DATEADD(customers[date], -1, QUARTER)
    )
    )
    )

     

    And one for new customers

    NewCustomers =
    CALCULATE(
    DISTINCTCOUNT(customers[guest_code]),
    EXCEPT(
    VALUES(customers[guest_code]),
    CALCULATETABLE(
    VALUES(customers[guest_code]),
    DATEADD(customers[date], -1, QUARTER)
    )
    )
    )

    • manar_alamri's avatar
      manar_alamri
      Icon for Helper I rankHelper I
      1 year ago

      Hi bhanu_gautam 

      I tried to do your solution but the value is showing  blank on RetainedCustomers

       

       

      and on NewCustomers the value shows the distinct guest code on the quarter ( it's not give me the new customers in the quarter)

       

  • lucadelicio's avatar
    lucadelicio
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    manar_alamri 
    try this measure:

    Customer Retention =
    VAR CurrentPeriodStart = STARTOFQUARTER(Table1[date])
    VAR CurrentPeriodEnd = ENDOFQUARTER(Table1[date])
    VAR PreviousPeriodStart = PREVIOUSQUARTER(STARTOFQUARTER(Table1[date]))
    VAR PreviousPeriodEnd = PREVIOUSQUARTER(ENDOFQUARTER(Table1[date]))

    RETURN
    CALCULATE(
    DISTINCTCOUNT(Table1[guest code]),
    FILTER(
    Table1,
    Table1[date] >= CurrentPeriodStart && Table1[date] <= CurrentPeriodEnd &&
    Table1[guest code] IN
    CALCULATETABLE(
    VALUES(Table1[guest code]),
    Table1[date] >= PreviousPeriodStart && Table1[date] <= PreviousPeriodEnd,
    Table1[Center] = EARLIER(Table1[Center])
    )
    )
    )



    You can adjust the period if you need monthly retention instead of quarterly by modifying the STARTOFQUARTER and ENDOFQUARTER functions to STARTOFMONTH and ENDOFMONTH respectively.

    Regards

  • lucadelicio's avatar
    lucadelicio
    Icon for Impactful Individual rankImpactful Individual
    1 year ago

    As attachment the pbix file.
    Try this measure:

    Customer Retention by Quarter =
    VAR CurrentQuarterStart = STARTOFQUARTER(Table1[date])
    VAR CurrentQuarterEnd = ENDOFQUARTER(Table1[date])
    VAR PreviousQuarterStart = STARTOFQUARTER(DATEADD(Table1[date], -1, QUARTER))
    VAR PreviousQuarterEnd = ENDOFQUARTER(DATEADD(Table1[date], -1, QUARTER))
    VAR Center = SELECTEDVALUE(Table1[Center])
    VAR PreviousGuest =
    CALCULATETABLE(
        DISTINCT(Table1[guest code])
        ,ALL(Table1)
        ,Table1[date] >= PreviousQuarterStart && Table1[date] <= PreviousQuarterEnd,
        Table1[Center] = Center
    )

    RETURN
    CALCULATE(
        DISTINCTCOUNT(Table1[guest code]),
        FILTER(
            ALL(Table1),
            Table1[date] >= CurrentQuarterStart && Table1[date] <= CurrentQuarterEnd &&
            Table1[guest code] IN PreviousGuest
        )
    )