Table with customer retention by month

[Defo]

Hi all,

I'm here for a very common issue, retention rate. I want to show in a table the number of customers of the specific month and the number of customers that have bought in the specific month and in the previous too.

I can only solve this problem at front end level, not have permission to access to load editor. Imagine that I have available a table for all transaction at CustomerCode and YearMonth level.
Somenthing like:

YearMonth	CustomerCode	Flag Buyer
2024-01	20	1
2024-01	21	1
2024-01	22	1
2024-01	23	0
2024-01	24	1
2024-02	20	1
2024-02	23	1
2024-02	39	1

Resulting in:

YearMonth	# Customers	# Retained Customer
2024-01	4	-
2024-02	3	2

I'm looking to a formula that can be evaluated at row level, letting me compute for each row how many customer have bought in the row's Month and in the previous.

Thanks,
Davide

[Qrishna]

Hi - is see only customer 20 is the only customer that  bought in both current month and previous month, so how is the retained customers = 2 in the second table when its actually 1 for 2024-02?

[Defo]

Sorry, my bad! Hoping that you got the point.

How did you get that result you are showing? That's exactly what I'm looking for.

Thanks, Davide

[Kushal_Chawda]

@Defo  try below expression for # Retained Customer

=sum(aggr(if(CustomerCode = Above(Only({1}CustomerCode)) and 
             YearMonth <> Above(Only({1}YearMonth)) and 
             (Above(Only({1}[Flag Buyer])=1 and [Flag Buyer]=1))
          ,1,0),
    CustomerCode,YearMonth))