Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculating ARR and Churn Rates

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 IDSale Order IDProd TypeSale TypeStart DateEnd DateARR
Cutomer A1PerpetualNew4/15/20184/14/2020                       100,000
Customer A1PerpetualNew4/15/20184/14/2020                         20,000
Customer B2TermNew6/16/20196/15/2021                         10,000
Customer C3PerpetualNew10/1/20209/30/2021                         60,000
Customer C3PerpetualNew10/1/20209/30/2021                         20,000
Customer A4TermRenewal (Late)8/6/20204/14/2021                       120,000
Customer B5TermUpsell9/20/20196/15/2021                         25,000
Customer C6PerpetualRenewal9/30/20219/30/2022                         61,800
Customer C6PerpetualRenewal9/30/20219/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

 

Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    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.

3 Replies