cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Re: Advanced Email Subscriber Churn Rate - Taking into Account Inactivity and Cohorts

@charleshale

 

So taking into account the comments on the previous posting, I believe your measure could be simplified a bit:

 

\SoftChurnersTRY3_V2 = 
      CALCULATE( 
        COUNTROWS( Table1 ) , 
            FILTER (Table1, Table1[Latest Interaction] < EDATE(Table1[Profile Created Date],12) ),
            FILTER(Table1, Table1[Engagement] <> "optout" && Table1[Engagement] <> "hardbounce" )
            )

 

where we make use of the beautiful row context available within FILTER( ) Smiley Happy Note we're not using the two additional measures you created.

 

Or we could write the condition in the second FILTER( ) by using the IN operator (no conceptual change, just a different syntax to state the very same):

 

\SoftChurnersTRY3_V3 = 
      CALCULATE( 
        COUNTROWS( Table1 ) , 
            FILTER (Table1, Table1[Latest Interaction] < EDATE(Table1[Profile Created Date],12) ),
            FILTER(Table1, NOT Table1[Engagement] IN { "optout" , "hardbounce"} )
            )

 

There are still some additional (small) changes that could be done. And maybe some modification to increase performance, although that will depend to a substantial extent on the nature of your data.

 

By the way, I'm curious. Why do you start the names of your measures with a "\"? Is it to group them alphabetically so that they show all together? 

 

Cheers

charleshale Regular Visitor
Regular Visitor

Re: Advanced Email Subscriber Churn Rate - Taking into Account Inactivity and Cohorts

@AIB - Good catch on the && vs II and simpler code.   Thank you.   The \ mark is just a mark I use for the ones I'm still trying to figure out.   

 

Here's a small improvement I made:

 

\SoftChurnersTRY3_v2.5 = 
 VAR _MosChurnPeriod = 12     
 RETURN     
      CALCULATE( 
        COUNTROWS( Table1 ) , 
            FILTER (Table1, Table1[Latest Interaction] < EDATE(Table1[Profile Created Date],_MosChurnPeriod) ),
            FILTER(Table1, Table1[Engagement] <> "optout" && Table1[Engagement] <> "hardbounce" )
            )

And here's one I tried that returned incorrect results in some periods, but correct in others -- probably that immutable issue again!  (that derives from thinking of functions like indirect() in excel 

 

\Bad_Softchurn =    //delivering flawed results
VAR _DateAsMeasure = 
    MAXX(
	    KEEPFILTERS(VALUES('Table1'[Profile Id])),
	    CALCULATE(MAX('Table1'[Profile Created Date])
    ))
VAR _ChurnMosMeasured = 12
VAR _ChurnDate = EDATE(_DateAsMeasure,_ChurnMosMeasured)

RETURN
       CALCULATE( 
        COUNTROWS( Table1 ) , 
            FILTER (Table1, Table1[Latest Interaction] < _ChurnDate ),
            FILTER(Table1, Table1[Engagement] <> "optout" && Table1[Engagement] <> "hardbounce" )
            )