DAX Function for Churn Analysis and Net Revenue Retention (NRR)

This document outlines a DAX function that connects a sales table with a customer table to classify customers as live or churned. It also computes the Net Revenue Retention (NRR) based on specified parameters.

Key Components

1. Sales Table: Contains data related to sales transactions.
2. Customer Table: Contains customer information including their status.
3. Net Revenue Retention Calculation: Uses Monthly Recurring Revenue (MRR) to determine NRR.

DAX Code Implementation

The following DAX measures are created to compute the necessary metrics:

1. Measure to Determine Churned Customers

Churned Customers = 
VAR CurrentMonth = MAX(Sales[TransactionDate])
RETURN 
    COUNTROWS(
        FILTER(
            Customer, 
            Customer[Status] = "Churned" && 
            Customer[ChurnDate] >= EOMONTH(CurrentMonth, -1) && 
            Customer[ChurnDate] < EOMONTH(CurrentMonth, 0)
        )
    )

2. Measure for Current Month MRR

Current Month MRR = 
SUMX(
    FILTER(
        Sales, 
        MONTH(Sales[TransactionDate]) = MONTH(TODAY()) && 
        YEAR(Sales[TransactionDate]) = YEAR(TODAY())
    ), 
    Sales[MRR]
)

3. Measure for Last Year MRR

Last Year MRR = 
SUMX(
    FILTER(
        Sales, 
        MONTH(Sales[TransactionDate]) = MONTH(TODAY()) && 
        YEAR(Sales[TransactionDate]) = YEAR(TODAY()) - 1
    ), 
    Sales[MRR]
)

4. Final Measure for NRR Calculation

Net Revenue Retention (NRR %) = 
VAR MRRCurrent = [Current Month MRR]
VAR MRRChurned = [Churned Customers] * AVERAGE(Sales[MRR]) // Assuming MRR from churned users
VAR MRRLastYear = [Last Year MRR]
RETURN 
    DIVIDE(
        MRRCurrent - MRRChurned, 
        MRRLastYear, 
        0 // Return 0 if denominator is 0
    )

Explanation of the Measures

• Churned Customers: This measure counts the number of customers who have churned within the current month. It filters the Customer table based on churn status and date.

• Current Month MRR: This measure calculates the total Monthly Recurring Revenue for the current month by summing the MRR from the Sales table.

• Last Year MRR: This measure computes the total MRR for the same month in the previous year.

• Net Revenue Retention (NRR %): This measure calculates the NRR percentage using the defined formula. It subtracts the churned MRR from the current month's MRR and divides by last year's MRR, providing insights into customer retention performance.

Code Usage Example

To use these measures in a Power BI report, simply include them in your data model and visualize them in dashboards or reports. For example, you could create a card visualization to show the NRR percentage alongside a table listing churned customers.

Conclusion

These DAX expressions provide a comprehensive framework for analyzing customer churn and calculating Net Revenue Retention. They can significantly enhance your business intelligence reports and decision-making processes. For further learning, consider exploring courses on the Enterprise DNA Platform to deepen your understanding of DAX and data modeling techniques.