Attrition calculation

AliB · October 21, 2020, 9:42pm

Hi
I want to be able to calculate the following attrition / increase values:

LOST customers ie where Contract £ = 0 and Contract £ PY > 0
Reduced customers ie where Contract £ < Contract PY

New customers ie where Contract £ > 0 and Contrct £ PY = 0
Increased Customer Value ie where Contract £ > Contract £ PY

Contracts can be any length 1 mth to 5 years

How can I do this?
C Anal Test.pbix (177.1 KB)

Harsh · October 22, 2020, 1:41am

Hello @AliB,

Thank You for posting your query onto the Forum.

Below is the formula as well as the screenshot of the result provided for the reference -

Customer Attrition = 
SWITCH( TRUE() , 
    [Contract PY] > 0 && SELECTEDVALUE( Dates[Year] ) = "2021" , "To Be Evaluated" , 
    [Contract CY] = 0 && [Contract PY] > 0 , "Lost Customers" , 
    [Contract CY] > 0 && [Contract PY] = 0 , "New Customers" , 
    [Contract CY] < [Contract PY] , "Reduced Customers" , 
    [Contract CY] > [Contract PY] , "Increased Customers" ,
    NOT( ISBLANK( [Contract CY] ) ) && NOT( ISBLANK( [Contract PY] ) ) && [Contract CY] = [Contract PY] , "No Change" , 
BLANK() )

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Please Note:

1. Since we’re not in the Year 2021, I’ve avoided the calculation of “Variances” and kept it as “Blanks” i.e. “[Contract CY] - [Contract PY]” because it will unnecessarily show the negative values and same goes for the “Customer Attrition” formula as well since it will simply show it as “Lost Customers” therefore I’ve added one more logic there known as “To Be Evaluated”. If you feel, you need to change the logic, you can edit the formula and get the desired results as per the requirements.

2. I’ve also modified the model and de-normalized the data.

Thanks and Warm Regards,
Harsh

Contract Data - Harsh.xlsx (10.0 KB)

Customer Attrition - Harsh.pbix (88.2 KB)

AliB · October 24, 2020, 9:59pm

Thanks Harsh for the suggestion. I’ve looked at it but it doesn’t seem to give me the correct answers …

Customer A for eg has 1 contract that started in 2018 and expires in 2021.
If I filter on FY19 it thinks that Customer A was lost when in fact there was no change in the contract value.

Somehow we need to be able to calculate the fact that Customer A was an active customer all the years between 2018 and 2021

Harsh · October 24, 2020, 11:55pm

Hello @AliB,

Well, this was a simple fix. Earlier in your file you’d calculated the Variance by using the formula as provided below -

Variance = [Contract CY] - [Contract PY]

So going by that logic I used the same formula earlier. Below is the formula provided for the reference -

Variance = 
SWITCH( TRUE() , 
    [Contract PY] = BLANK() , BLANK() , 
    SELECTEDVALUE( Dates[Year] ) <> "2021" , [Contract CY] - [Contract PY] )

Now, as you mentioned that “Customer A” is an active customer, then in that case I guess “Customer G” is also an active customer since it’s contract expiry date is 30th Nov, 2020. And same goes for “Customer C” where although contract has already been expired on 11th Sep, 2020 we cannot rule them as lost/reduced customer since there’s no new contract in place at a revised price. So now, going by this logic below is the formula provided for the reference -

Variance = 
SWITCH( TRUE() , 
    [Contract CY] = BLANK() , BLANK() , 
    [Contract PY] = BLANK() , BLANK() , 
    SELECTEDVALUE( Dates[Year] ) <> "2021" , [Contract CY] - [Contract PY] )

Now, if you apply the filter on respective financial years you shall see the correct variance results. Below are the screenshots provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’re looking for.

Thanks and Warm Regards,
Harsh

Customer Attrition - Harsh v2.pbix (88.5 KB)

Harsh · October 25, 2020, 12:31am

Hello @AliB,

Also in previous file, although I correct the logic of variance I missed to correct the logic of “Customer Attrition”. Below is the formula provided for the reference of “Customer Attrition” as per the logic mentioned above in my previous post -

Customer Attrition = 
SWITCH( TRUE() , 
    [Contract PY] > 0 && SELECTEDVALUE( Dates[Year] ) = "2021" , "To Be Evaluated" , 
    SELECTEDVALUE( Dates[Date] ) > SELECTEDVALUE( 'Test Data'[Contract End Date] ) && [Contract CY] = 0 && [Contract PY] > 0 , "Contract To be Renewed" ,
    [Contract CY] = 0 && [Contract PY] > 0 , "Retained Customers" , 
    [Contract CY] > 0 && [Contract PY] = 0 , "New Customers" , 
    [Contract CY] < [Contract PY] , "Reduced Customers" , 
    [Contract CY] > [Contract PY] , "Increased Customers" ,
    NOT( ISBLANK( [Contract CY] ) ) && NOT( ISBLANK( [Contract PY] ) ) && [Contract CY] = [Contract PY] , "No Change" , 
BLANK() )

Also attaching the PBIX file of the working for the reference.

Thanks and Warm Regards,
Harsh

Customer Attrition - Harsh v3.pbix (88.4 KB)

AliB · October 25, 2020, 12:51am

Thanks Harsh

Customer A is still showing as Lost when it has an active contract

The contract that expired in September should be showing as lost because it’s now October and no replacement contract exists

Do I need to split the contracts into year records so for eg Customer A’s would be 3 lines with a start date in each year it was active?

Harsh · October 25, 2020, 1:21am

Hello @AliB,

Please refer my previous post where I’ve correct the “Customer A” status. But anyways, since new condition has been specified about the “Lost Customers”. Below is the revised formula provided for the reference -

Customer Attrition = 
SWITCH( TRUE() , 
    [Contract PY] > 0 && SELECTEDVALUE( Dates[Year] ) = "2021" , "To Be Evaluated" , 
    SELECTEDVALUE( Dates[Date] ) > SELECTEDVALUE( 'Test Data'[Contract End Date] ) && [Contract CY] = 0 && [Contract PY] > 0 , "Lost Customers" ,
    [Contract CY] = 0 && [Contract PY] > 0 , "Retained/Active Customers" , 
    [Contract CY] > 0 && [Contract PY] = 0 , "New Customers" , 
    [Contract CY] < [Contract PY] , "Reduced Customers" , 
    [Contract CY] > [Contract PY] , "Increased Customers" ,
    NOT( ISBLANK( [Contract CY] ) ) && NOT( ISBLANK( [Contract PY] ) ) && [Contract CY] = [Contract PY] , "No Change" , 
BLANK() )

So now as per the logic, “Customer A” still continues to be shown as “Active Customer”, “Customer C” is being considered as “Lost Customer” because contract has already been expired on 11th Sep, 2020 and we’re evaluating it’s status on 12th Sep, 2020. This same goes for “Customer G” as well although it’s contract expiry date is 30th Nov, 2020 but we’re evaluating it’s status on 1st Dec’2020 so it’s being as “Lost Customer”. Below is the screenshot provided of the end result -

Attaching the PBIX file of the working for the reference.

Thanks and Warm Regards,
Harsh

Customer Attrition - Harsh v4.pbix (88.4 KB)

EnterpriseDNA · October 27, 2020, 2:16am

Hi @AliB, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

AliB · October 28, 2020, 10:03pm

Thanks Harsh

How can I evaluate all the contracts at one particular date? for eg as at 31st Dec I want to see how the variance between active contracts 31st Dec 2020 and the same time last year
If I choose FY20 Customer A doesn’t show in the list at all?

Do I need to split multi year contracts into a row per year and then use some sort of cross join?

Harsh · October 29, 2020, 12:36am

Hello @AliB,

You cannot view the contract by selecting a particular date since the data is on a periodic basis. But what you can do is something like this. Below is the screenshot provided for the reference -

So, if you select only FY20 it is as good as that you’re viewing the data as on 31st December, 2020 since it’s showing the data for the whole financial year.

Now, coming back to query that you’ve asked about “Customer A” which was not visible at all. So for this I’ve bifurcated the “Customer A” into 3 line items periodically for each year. So now, “Customer A” will be visible for all the years.

Important Note:

So it’s important to break the data into individual line items wherever any particular customer has a contract of more that one year.
The customers whose contract has been expired has been termed as “Lost Customers” as recommended by you.
The customers are termed as “Retained Customers” where “Contract CY = Contract PY” and these same goes for the customers whose contract is till the year 2021. Their status has been changed from “To Be Evaluated” since they’ve their contracts in place till 2021.

So now, the results looks like this for each Financial Years. Below are the screenshots provided for the reference where all the customers are being categorized as per the conditions specified by you -

Therefore, if we remove the filters from the Financial Years the overall/final/end result will be like this -

I’m also attaching the Excel as well as PBIX file of the working for the reference.

Hoping this meets your requirements.

Thanks and Warm Regards,
Harsh

Customer Attrition - Harsh.xlsx (10.1 KB)

Customer Attrition - Harsh v5.pbix (91.4 KB)

EnterpriseDNA · October 29, 2020, 3:27am

Hi @AliB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!