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 cente...
Need Help
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)