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.