Extension of the DAX Code for Customer Relation

Existing Code Analysis

The provided code snippet calculates the Net Revenue Retention Rate (NRR) for a set of sales over the last 12 months. The calculation considers the total revenue and lost revenue from customers who are defined as lost in a separate table (presumably LostCustomers). The formula also ensures that if the total revenue is zero, it returns zero to avoid division errors.

Objective

The goal is to extend the existing DAX code to include a relation to the customer table, allowing us to filter by customer names and indicate active or non-active status. This will help provide more granular insights into the retention calculations based on customer activity.

Extended Code Implementation

Here’s the extended DAX code:

NetRevenueRetentionRate =
VAR Last12Months = DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -12, MONTH)
VAR ActiveCustomers =
    CALCULATETABLE(
        VALUES(Customers[CustomerName]),
        Customers[IsActive] = TRUE()  // Assuming IsActive is the column indicating active status
    )
VAR TotalRevenue =
    CALCULATE(
        SUM(Sales[Revenue]),
        Last12Months,
        Sales[CustomerName] IN ActiveCustomers  // Filter for active customers
    )
VAR LostRevenue =
    CALCULATE(
        SUM(Sales[Revenue]),
        LostCustomers,
        Last12Months,
        Sales[CustomerName] IN ActiveCustomers  // Filter for active customers
    )
RETURN
    IF(TotalRevenue > 0, (TotalRevenue - LostRevenue) / TotalRevenue, 0)

Explanation of Changes

1. Active Customers Variable: Introduced a variable ActiveCustomers that calculates a table of customer names filtered to only include those where IsActive is TRUE.

2. Total Revenue Calculation: Modified the TotalRevenue calculation to include a filter for customers that are active using the IN clause, which ensures that only sales related to active customers are considered.

3. Lost Revenue Calculation: Similarly, updated the LostRevenue calculation to consider only the active customers for the lost revenue calculation to maintain consistency.

Best Practices

• Naming Conventions: Maintained descriptive variable names for clarity.
• Code Organization: Structured the code into easily readable components (variables).
• Error Handling: Continued to ensure that division by zero is handled gracefully.

Conclusion

The extended DAX code now integrates the customer activity information, ensuring that the Net Revenue Retention Rate reflects only those actively engaging with the services. This change enhances analysis and provides a more detailed view of retention metrics based on active customer status.

For further learning and more complex DAX functionalities, consider exploring courses offered on the Enterprise DNA Platform.