Forum Discussion
Calculating ARR and Churn Rates
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 , 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