DAX Function to Calculate Monthly Net Revenue Retention (NRR)

This guide provides a DAX function to calculate the Monthly Net Revenue Retention (NRR) over a period of 12 months by connecting a sales table with a customer table. NRR is a critical metric for understanding revenue growth from existing customers.

Assumptions

• Sales Table: Contains columns SalesAmount, CustomerID, and Date.
• Customer Table: Contains columns CustomerID and CustomerStartDate.
• You have an appropriate relationship set up between the Sales and Customer tables via CustomerID.

Function Overview

NRR_Monthly = 
VAR CurrentMonth = MAX(Sales[Date]) 
VAR PreviousMonth = EDATE(CurrentMonth, -1) 
VAR StartMonth = EDATE(CurrentMonth, -11) // 12 months window

RETURN
    SUMX(
        DATESINPERIOD(Sales[Date], CurrentMonth, -12, MONTH),
        VAR CurrentSales =
            CALCULATE(
                SUM(Sales[SalesAmount]),
                FILTER(
                    Sales,
                    Sales[Date] >= StartMonth && Sales[Date] <= CurrentMonth
                )
            )
        VAR ChurnedSales =
            CALCULATE(
                SUM(Sales[SalesAmount]),
                FILTER(
                    Sales,
                    Sales[Date] >= StartMonth && Sales[Date] < PreviousMonth &&
                    NOT(CALCULATE(COUNTROWS(Customer), Customer[CustomerID] = Sales[CustomerID])) > 0
                )
            )
        VAR ExpansionSales =
            CALCULATE(
                SUM(Sales[SalesAmount]),
                FILTER(
                    Sales,
                    Sales[Date] >= CurrentMonth
                )
            )
        RETURN
            DIVIDE(CurrentSales - ChurnedSales + ExpansionSales, CurrentSales, 0) // to prevent division by zero
    )

Explanation of the Function

Steps:

1. Variable Definitions:

   • CurrentMonth: The latest month from the Sales data.
   • PreviousMonth: One month prior to the current month.
   • StartMonth: The month 12 months prior to the current month to create a rolling window.

2. Root Calculation Using SUMX:

   • The calculation iterates over a 12-month period obtained from DATESINPERIOD.

3. Calculation of Revenue Components:

   • Current Sales: Total sales in the range from StartMonth to CurrentMonth.
   • Churned Sales: Sales lost from customers who did not make purchases during the current month but were previously counted.
   • Expansion Sales: Sales attributed to existing customers who increased their spend in the current month.

4. Final NRR Calculation:

   • Divide the retained revenue (Current Sales - Churned Sales + Expansion Sales) by Current Sales.
   • Handle division by zero gracefully using the DIVIDE function.

Code Usage Example

To visualize the NRR on your reports:

1. Add the NRR_Monthly measure to a line or card visual in your report.
2. Filter by date to show the NRR trend over the past 12 months.

Conclusion

This DAX measure efficiently computes Monthly NRR by integrating sales with customer data while handling possible edge cases in the calculations. Consider exploring more advanced courses on the Enterprise DNA Platform to further your DAX skills and understanding.