Customers Retention by quarters

[manar_alamri]

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.

[lucadelicio]

Try to download my pbix and to compare.

[lucadelicio]

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

    )

)

[manar_alamri]

@lucadelicio 

I tried to remove the center (  Table1[Center] = Center) 

and it is work

but i need it by centers

[manar_alamri]

 @lucadelicio  Still I faced an issue 

 the result appears blank 

I dont know why 

[lucadelicio]

@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

[manar_alamri]

@lucadelicio 

I tried to do the measure but I faced these errors 

[lucadelicio]

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.

[manar_alamri]

Thank you @lucadelicio  the new client measure is work !

can you help me on Customers retention also ?

[bhanu_gautam]

@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]

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)