cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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"

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors