cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
Solution Sage

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"

Solution Sage

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"

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors