Calculating ARR and Churn Rates

[Anonymous]

Hello,

I am trying to find the most elegant way to solve for ARR (Annual Reccurring Revenue) at a given time while also setting up my model to be able to calculate churn rates. I have a sales product table that has the following:

Customer ID	Sale Order ID	Prod Type	Sale Type	Start Date	End Date	ARR
Cutomer A	1	Perpetual	New	4/15/2018	4/14/2020	100,000
Customer A	1	Perpetual	New	4/15/2018	4/14/2020	20,000
Customer B	2	Term	New	6/16/2019	6/15/2021	10,000
Customer C	3	Perpetual	New	10/1/2020	9/30/2021	60,000
Customer C	3	Perpetual	New	10/1/2020	9/30/2021	20,000
Customer A	4	Term	Renewal (Late)	8/6/2020	4/14/2021	120,000
Customer B	5	Term	Upsell	9/20/2019	6/15/2021	25,000
Customer C	6	Perpetual	Renewal	9/30/2021	9/30/2022	61,800
Customer C	6	Perpetual	Renewal	9/30/2021	9/30/2022	20,600

I have a seperate table for customers with a few details in that table. Just, Customer ID, Name, Region, etc. 

I have quite a few different but related things I am trying to get from this data:

• Active ARR as of a given date. For example, on 3/30/19 the active ARR would be $120K. On 3/31/20 the active ARR would be $155K.
• Active Customers on a given date, 3/30/19 it would be 1 and for 3/31/20 it would be 2.
• ARR added in a given period and the number of new customers. For example, the ARR added for Q2 2018 is $120K and the number of new customers is 1 (Customer A).
• Churn in a given period
  • This is a bit tricky because at the end of April 2020 we would show $120K and 1 Customer Churned (Customer A). When they renew late in August we would offset churn with $120K of late renewals and add in a customer from late renewals. 
  • Customer C would never show as churned, as they renewed before their expiration. 
• Churn rates, both by $ and by #. We take the active customers/dollars at a given date as the denominator, and the churn over the following 12 months as the numerator. 

My current model for getting this information has a calculated comlumn for each month on the sales prod table that calculates if the ARR was active during that month. I then unpivoted that table so that there is a new table with a row for each product and each month and then I calculate a few other columns to get the details I am looking for. Then I have a by customer table that summerizes the unpivoted product table and has a few more calculated columns to get some of the customer count information. To get churn rates I pull the monthly details into excel. 

I'm looking for a more elegant solution, even if it just starts with a way to calculate active ARR $ and # of Customers as of a given date, and added customers and ARR $ withoug creating columns for every time period. 

I appreciate any help. 

Thank you,

Erin

[amitchandak]

@Anonymous , there are quite a few things there. You have either follow HR Blog or the attached file after signature to get the daily number : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Then refer to Customer Retention blogs

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

[DataZoe · Accepted solution]

@Anonymous You can use the measures @amitchandak has provided in his HR Analysis post to help you with many of these measures: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

First, you would create a disconnected date table -- that is, a date table that does not have a set relationship with the data table. Modeling --> New Table. Once created, go to Table tools --> Mark as Date Table.

Date =
CALENDAR ( MIN ( 'Table'[Start Date] ), MAX ( 'Table'[End Date] ) )

Then you can calculate your current customers at any date via this measure:

Current Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer ID] ),
    FILTER (
        'Table',
        'Table'[Start Date] <= MAX ( 'Date'[Date] )
            && (
                ISBLANK ( 'Table'[End Date] )
                    || 'Table'[End Date] > MAX ( 'Date'[Date] )
            )
    )
)

And the ARR at any date with this measure:

Current ARR =
CALCULATE (
    SUMX (
        FILTER (
            'Table',
            'Table'[Start Date] <= MAX ( 'Date'[Date] )
                && (
                    ISBLANK ( 'Table'[End Date] )
                        || 'Table'[End Date] > MAX ( 'Date'[Date] )
                )
        ),
         ( 'Table'[ARR] )
    )
)

Edit: I changed the Current Customers to do a DISTINCTCOUNT instead of COUNTX.

[Anonymous]

Thank you, this has really helped me get started building a better model.