DAX Function for Customer Churn Analysis and NRR Calculation

This document outlines a DAX function that connects a sales table with a customer table to evaluate customer status (live vs. churned) and calculate Net Revenue Retention (NRR). The NRR formula utilizes Monthly Recurring Revenue (MRR) for current and last year while accounting for revenue loss from churned customers over the past 12 months.

Assumptions and Table Structures

• Sales Table: Contains sales records with columns for CustomerID, SalesAmount, and SaleDate.
• Customer Table: Contains customer records with columns for CustomerID, IsChurned, and ChurnDate.

DAX Implementation

Definitions

1. Current MRR: The sum of MRR for active customers in the current month.
2. Churned Revenue (Rolling 12): Revenue from customers who have churned within the last 12 months.
3. MRR Last Year: The MRR from the same period in the last year.

DAX Code

// Function to calculate Net Revenue Retention (NRR)
NRR_Calculation = 
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())

// Calculate current month MRR for live customers
VAR CurrentMRR =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        FILTER(
            Sales,
            Sales[SaleDate] >= EOMONTH(TODAY(), -1) + 1 && 
            Sales[SaleDate] <= EOMONTH(TODAY(), 0) &&
            RELATED(Customers[IsChurned]) = FALSE
        )
    )

// Calculate churned revenue rolling 12 months
VAR ChurnedRevenueRolling12 =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        FILTER(
            Sales,
            Sales[SaleDate] >= EDATE(TODAY(), -12) &&
            Sales[SaleDate] <= TODAY() &&
            RELATED(Customers[IsChurned]) = TRUE
        )
    )

// Calculate MRR for the same month last year
VAR MRRLastYear =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        FILTER(
            Sales,
            MONTH(Sales[SaleDate]) = CurrentMonth &&
            YEAR(Sales[SaleDate]) = CurrentYear - 1
        )
    )

// Calculate NRR
VAR NRR =
    DIVIDE(
        CurrentMRR - ChurnedRevenueRolling12,
        MRRLastYear,
        0 // Handle division by zero case
    )

RETURN
    NRR // Return the net revenue retention percentage

Explanation of Code Sections

1. Variable Declarations:

   • CurrentMonth and CurrentYear store the current month and year for easy reference.

2. Current MRR Calculation:

   • Uses SUM to aggregate SalesAmount for the current month, filtering for active customers while ensuring only sales between the start and end of the current month are included.

3. Churned Revenue Rolling 12 Months:

   • Calculates the total revenue from customers who have churned in the last 12 months using a time-based filter.

4. MRR Last Year Calculation:

   • Similar to current MRR but filters based on the same month for the previous year.

5. NRR Calculation:

   • Uses DIVIDE to compute the Net Revenue Retention ratio, ensuring that division by zero is handled gracefully by returning zero.

Code Usage Example

To utilize the NRR_Calculation measure in a Power BI report, follow these steps:

1. Create the measure in your data model.
2. Drag the measure into your report canvas to visualize NRR.
3. Filter results by time period (month, year) to analyze performance over time.

Conclusion

This DAX function effectively connects sales data with customer churn information, allowing users to monitor the health of their customer base and measure overall retention. For a comprehensive understanding of DAX and data modeling, consider exploring resources available on the Enterprise DNA Platform.

This implementation is efficient, scalable, and adheres to best practices in software engineering for data analytics.