Issue with Attrition Analysis

Hi,

I have a situation where I’m trying to show ONLY those customers that are Lost or New (similar to your Attrition Analysis Summit ( Attrition Analytics & Virtual Tables | Enterprise DNA

First I created a parameter table (called Churn Time Period) that stores values ranging from 30 to 390 (Churn Time Period = GENERATESERIES(0, 390, 30 ). This is shown as a filter on the page and allows users to select which range to calculate and show the customers and \$\$ we lost or gained within that time period. I use TODAY() as the starting point.

EXAMPLE: If a user selects 90 days from the filter, then it calculates how many new or lost customers we have in the 90 day period from today. Assuming today is 10/1/21, how many customers did we lose between 7/3/21 and 10/1/21 and how many new customers did we gain in that same period? And what is the \$\$ value of those customers?

Everything works as expected until I was asked to show the net gain/loss between new customers and lost customers. For example, suppose we lost 5 customers within that 90 days period and we gained 3, that makes the net loss of customers 2. However, suppose the \$\$ lost is \$50,000 (from those 5 customers) but the \$\$ gained is \$150,000 (from the 3 new customers). So even though we lost a net of 2 customers, our financial gain is 200%.

HERE’S THE PROBLEM:
I’m trying to create a measure that looks ONLY at Lost customers (to get a \$\$ value) so I can compare it against another measure that looks ONLY at New customers. If I can get this, I can create my Net Gain/Loss measure. But the problem is that my measures only work if I filter the visual. As soon as I add a filter to the measure itself, it doesn’t work. It only shows the total sales.

In my measure I have a flag that’s 1 if New, 0 if not new. But as you can see from the attached file, even though my measure filters out anything <> 1, it still shows ALL the sales.

OTHER NOTES:
I have a few more categories other than Lost or New. I also have to figure on Steady and Returning customers too. I created a filter that I can choose between each of these categories (which I’ve already done), but this won’t work until I solve the above problem.

Please let me know if you need any other information.

Bumping this post for more visibility.

I received the solution from another forum. Thank you.

It turns out I was referencing the wrong table in my measure

My measure:

New Customers = calculate(

``````            [Sum Sales],

filter( 'Churn Time Period', 'Churn Time Period'[New Customers] = 1 ) )
``````

Correct measure:

_New = CALCULATE (

``````            [Sum Sales],

FILTER ( Customers, [New Customers] = 1 ) )``````