DAX Formula for Calculating NRR Rate for Customers 12 Months Back

Definition of NRR

Net Revenue Retention (NRR) is a metric that measures the percentage of recurring revenue retained from existing customers over a specific time period, factoring in upgrades, downgrades, and churn.

Formula Explanation

The following DAX formula calculates the NRR rate for customers from the previous 12 months.

DAX Formula

NRR_Rate_12_Months = 
VAR Total_Revenue_Last_Year = 
    CALCULATE(
        SUM(Sales[Revenue]),
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
    )
    
VAR Churned_Revenue_Last_Year = 
    CALCULATE(
        SUM(Sales[Revenue]),
        'Customer'[Status] = "Churned",
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
    )

RETURN
IF(
    Total_Revenue_Last_Year <> 0,
    (Total_Revenue_Last_Year - Churned_Revenue_Last_Year) / Total_Revenue_Last_Year,
    BLANK()
)

Breakdown of the Formula

1. Variables:

   • Total_Revenue_Last_Year: This variable calculates the total revenue generated over the last 12 months using the SUM() function, with DATESINPERIOD() to filter the date range.
   • Churned_Revenue_Last_Year: This variable calculates the revenue from customers that have churned within the same 12-month period. It applies a filter to sum revenue where customer status is marked as "Churned".

2. Return Statement:

   • The formula returns the NRR rate as a ratio of retained revenue (total revenue minus churned revenue) to the total revenue from last year.
   • The IF function ensures that the division occurs only when total revenue is not zero, returning a blank value otherwise to avoid division by zero errors.

Practical Example

Assuming you have a table named Sales with a revenue column and a related Customer table that tracks customer status, this formula will yield the NRR rate for the last 12 months based on current date contexts.

• Data Context:
  • If today is July 1, 2023, the calculation will focus on revenue from July 1, 2022, to June 30, 2023.
  • It aggregates all revenue during that time frame, deducts revenue from customers marked as churned, and then divides by the total revenue of that time frame.

Conclusion

This DAX formula provides a straightforward method for calculating the NRR rate over the past year. Users can apply this to monitor customer retention effectively and gauge overall business health.

For further understanding and enhancement of DAX skills, consider exploring resources on the Enterprise DNA Platform, which offers comprehensive training on DAX and related analytics skills.