Calculating Returned customers by customer and plan

[Mike282]

Hi all,

So I've got a dilemma. I'm trying to calculate returned customers but for a specific plan rather than looking at the customer as a whole. So just a bit of background. Our product is a monthly renewal based subscription. Every customer has a ID, a subscription Plan and and renewal dates. As it's monthly you expect 1 row per month per plan. So a sample data would be:

Customer ID	Subscription Plan	Renewal Date	Quantity
123	Plan 1	1/01/2021	1
123	Plan 2	1/01/2021	1
123	Plan 1	1/02/2021	1
123	Plan 2	1/02/2021	1
123	Plan 1	1/03/2021	1
123	Plan 2	1/03/2021	1
123	Plan 1	1/04/2021	1
123	Plan 2	1/04/2021	1
123	Plan 1	1/05/2021	1
123	Plan 2	1/05/2021	1
123	Plan 1	1/06/2021	1
123	Plan 2	1/06/2021	1
123	Plan 1	1/07/2021	1
123	Plan 2	1/07/2021	1
123	Plan 1	1/08/2021	1
123	Plan 2	1/08/2021	1
123	Plan 1	1/09/2021	1
123	Plan 1	1/10/2021	1
123	Plan 1	1/11/2021	1
123	Plan 2	1/11/2021	1
123	Plan 1	1/12/2021	1
123	Plan 2	1/12/2021	1

Note this is the subscription of only one customer as noted by the fact they all have the same customer ID. They've subscribed to 2 types of plans. Both plans are monthly as noted by the monthly payments. Note that for the customer with their plan 2 subscription, they lapsed payment in September and then returned in November.

I'm trying to calculate the returned value of 1 for that customer for a specific plan at the specific date from which they returned for that plan. In the case of this example it's for Plan 2 and a returned value of 1 on the 1st of November 2021 as they lapsed payment in September 2021.

Any help would be greatly appreciated. It's doing my head in lol.

Kind regards,

Mike 

[Anonymous · Accepted solution]

As usual, there are many ways. But I am giving you a DAX code which you will understand what is happening in each step.

ReturnCount = 
VAR CurrentCustomer = Subsriptions[Customer ID]
VAR CurrentPlan = Subsriptions[Subscription Plan]
VAR CurrentRenewalDate = Subsriptions[Renewal Date]
VAR FilteredTable = FILTER(
                Subsriptions,
                Subsriptions[Customer ID]=CurrentCustomer &&
                Subsriptions[Subscription Plan] = CurrentPlan &&
                Subsriptions[Renewal Date] < CurrentRenewalDate
            )
VAR PreviousRenewalDate = 
        MAXX(
            FilteredTable
            ,Subsriptions[Renewal Date]
            )
        
VAR Difference = 
    IF(
        isempty(FilteredTable),
        0,
        CurrentRenewalDate-PreviousRenewalDate
    )
RETURN IF(Difference>31,1,0)

In this example, The table name is "Subscriptions"