Calculation of Net Revenue Retention (NRR) Rate in DAX

Definition of NRR

Net Revenue Retention (NRR) is a key performance indicator that measures the percentage of recurring revenue retained from existing customers over a specific period of time, including the effects of upgrades, downgrades, and churn.

NRR Formula

The formula for calculating the NRR rate can be expressed as:

[ \text{NRR} = \frac{\text{Starting Monthly Recurring Revenue (MRR)} + \text{Expansion MRR} - \text{Churned MRR}}{\text{Starting Monthly Recurring Revenue (MRR)}} \times 100 ]

Where:

• Starting MRR: The recurring revenue at the beginning of the period.
• Expansion MRR: Additional revenue generated from existing customers, such as upsells or cross-sells.
• Churned MRR: Revenue lost from existing customers who have canceled their subscriptions.

DAX Formula

To calculate the NRR Rate in DAX, you can use the following formula:

NRR Rate = 
VAR StartingMRR = CALCULATE(SUM(Revenue[MRR]), Revenue[Date] = MIN(Revenue[Date]))
VAR ChurnedMRR = CALCULATE(SUM(Revenue[MRR]), Revenue[Status] = "Churned")
VAR ExpansionMRR = CALCULATE(SUM(Revenue[MRR]), Revenue[Status] = "Expansion")
RETURN
    DIVIDE((StartingMRR + ExpansionMRR - ChurnedMRR), StartingMRR) * 100

Explanation of the DAX Formula

1. Starting MRR: This variable calculates the total MRR at the beginning of the reporting period by summing the MRR column where the date corresponds to the minimum date in the dataset.
2. Churned MRR: This variable aggregates the total MRR that has churned by summing the MRR values where the subscription status is marked as "Churned".
3. Expansion MRR: This variable calculates the total upsell or cross-sell revenue generated from existing customers by summing the MRR values where the subscription status is "Expansion".
4. Return Statement: The formula calculates the NRR by combining the above variables, dividing the net revenue retained from existing customers by the starting MRR, and multiplying by 100 to express it as a percentage.

Practical Example

Assume a company has the following data for its recurring revenue in a monthly reporting table:

In this example:

• Starting MRR = 1000
• Churned MRR = 200
• Expansion MRR = 300

Applying the formula:

• NRR Calculation:
  [ \text{NRR} = \frac{1000 + 300 - 200}{1000} \times 100 ]
  [ \text{NRR} = \frac{1100}{1000} \times 100 = 110% ]

This indicates a positive NRR, showing the company has not only retained but also expanded its revenue from existing customers.

Conclusion

This DAX formula effectively allows you to calculate the NRR rate, enabling businesses to assess their performance in retaining and expanding revenue from existing customers. For further learning opportunities and advanced insights into DAX and data modeling, consider exploring resources available on the Enterprise DNA Platform.