cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper III

## Calculating Returned customers by customer and plan

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

Anonymous
Not applicable

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"