Need assistance with FILTER in a measure for Attrition Analysis

[Roseventura]

Hi,

Can you NOT filter a measure using the results of another measure?

I have a measure which returns a 1 or 0, depending on whether the customer is "New" within a user-selected time period.  I have another measure for "Lost" Customers which returns the number of days between the last sale and today otherwise returns 999999.

The measures are:

New Customers =

VAR ChurnDate = SELECTEDVALUE('Churn Time Period'[Churn Time Period]) )

VAR FirstSale = MINX(Shipments, Shipments[Date] )

VAR Calc = If (

and(FirstSale < today(), FirstSale > today() - ChurnDate ),

1, 0 )

// how many clients are new in churn period (New = 1)

RETURN

  Calc

Lost Customers =

VAR ChurnDate = 'Churn Time Period'[Churn Time Period Value]

VAR LastSale = MAXX(Shipments, Shipments[Date] )

var Calc = If (

and(LastSale < today(), LastSale > today() - ChurnDate - 1 ),

value(today() - lastSale),

999999 )

// how many clients are lost in churn period (Lost = 999999)

RETURN

Calc

I created a parameter table (called Churn Time Period) that stores values ranging from 30 to 390. This is shown as a filter on the page and allows users to select which day range to use.  TODAY() is the starting point.

Churn Time Period = GENERATESERIES(0, 390, 30 )

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 or gain between 7/3/21 and 10/1/21 and what is the $$ value in sales for those customers?

HERE’S THE PROBLEM:

Up until now, I was using this measure for calculating Total Shipments (Sales):

Total Sales = calculate(sumx(Shipments, Shipments[Ext Unit Price]))

and filtering a table Visual to only show New Customers = 1.  I have a separate table visual to show Lost Customers.   Everything works as expected until I was asked to show the net gain/loss between new customers and lost customers.

I created a measure that look ONLY at Lost customers so I can compare it against another measure that looks ONLY at New customers:

Total Summary Sales New Customers =

calculate([Total Summary Sales],

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

Total Summary Sales Lost Customers =

calculate([Total Summary Sales],

filter( 'Churn Time Period', 'Churn Time Period'[Lost Customers] = 999999 ) )

But these don't work I'm guessing because i'm using the results of a measure as the filter?  When I add the above measures to a visual, I only get the Total Sales, not sales for New Customers and Lost Customers.

Here's the result I get:

 

Here's the result I need, but I need these totals to appear in 1 table.  These 2 tables are filtered at the visual level. 

Please let me know if you need any other information.

[v-eqin-msft]

Hi @Roseventura ,

I was asked to show the net gain/loss between new customers and lost customers.

Since the customer name is very different, how to get the gain/loss? Do you only need to use the Totals to calculate the difference?

Please show us what's your expected.

Best Regards,
Eyelyn Qin

[Roseventura]

Do you only need to use the Totals to calculate the difference?

Yes, I only need to show the total net gain/loss (for all customers) but within the selected time period.

Thanks!

[PaulDBrown · Accepted solution]

Please try:

Sum Sales = SUM(Shipments[Ext Unit Price])

Balance =
VAR _New =
    CALCULATE ( [Sum Sales], FILTER ( Customers, [New Customers] = 1 ) )
VAR _Lost =
    CALCULATE ( [Sum Sales], FILTER ( Customers, [Lost Customers] = 999999 ) )
RETURN
    _New - _Lost

And you will get:

I've attached the sample PBIX file

[Roseventura]

Wow wow wow.  That's it!  It works!

So grateful for the solution.  I can't believe that it all came down to the fact that I was referencing the wrong table!

Your measure:

_New = CALCULATE (

                [Sum Sales],

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

My measure:

New Customers = calculate(

                [Sum Sales],

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

Thank you so much!

[Roseventura]

I'm not sure how to upload a sample PBIX to this forum.  My PBIX is not published yet so it doesn't exist anywhere on the web.  Also, if I were to publish it to my company's Power BI Service, outside ppl would not be able to access it.

Which ICON do I use to upload a PBIX?

😞

[PaulDBrown]

You can share the link to the file from a cloud service (OneDrive, Google Drive...) but PLEASE hide confidential information

[PaulDBrown]

Please let me know if you need any other information.

Please provide a sample PBIX file or sample data in tabular form (non-confidential)