Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Churn Rate Measure

Hey guys, I’d really appreciate to get some insights/help.   I’ve been struggling for quite some time to get my Churn Rate measure in place. None of the existing posts have helped me so far.   A...
  • Anonymous's avatar
    Anonymous
    7 years ago

    The assumption is that you have a proper Date table in the model that's not connected to your Accounts table (Dates). Once this is in place, you can write:

     

    [# Acc Lost] =
    var __periodStart = MIN ( Dates[Date] )
    var __periodEnd = MAX ( Dates[Date] )
    var __accCount =
    	CALCULATE(
    		COUNTROWS( Accounts ),
    		Accounts[Account Initial Date] < __periodStart,
    		Accounts[Account Final Date] >= __periodStart,
    		Accounts[Account Final Date] <= __periodEnd
    	)
    return
    	__accCount
    	
    [# Acc at Beginning] =
    var __periodStart = MIN ( Dates[Date] )
    var __periodEnd = MAX ( Dates[Date] )
    var __accCount =
    	CALCULATE(
    		COUNTROWS( Accounts ),
    		Accounts[Account Initial Date] <= __periodStart,
    		OR(
    			ISBLANK( Accounts[Account Final Date] ),
    			Accounts[Account Final Date] >= __periodStart
    		)
    	)
    return
    	__accCount
    
    [Account Churn] = DIVIDE( [# Acc Lost], [# Acc at Beginning] )

     

    You should not slice by [Account Final Date] and [Account Initial Date]. These fields should be hidden.

     

    Best

    Darek