Extension of DAX Code for Bar Chart Representation

Existing Code Analysis

The original DAX code calculates the Net Revenue Retention Rate (NRR) by analyzing the total revenue and lost revenue over the last 12 months. It uses the DATESINPERIOD, CALCULATE, and IF functions to derive the result based on sales data.

Components of the Original Code:

• Last12Months: Defines a time period of the last 12 months.
• TotalRevenue: Calculates the total revenue within that time frame.
• LostRevenue: Computes the revenue lost from customers during that period.
• Return Value: Returns the NRR as a percentage of retained revenue.

Objective

To extend the existing code by creating a bar chart that visualizes the NRR per month over the last 12 months, enabling a better representation of trends over time without disrupting the original purpose of the code.

Extended DAX Code

Below is the extended DAX code that computes the NRR on a monthly basis and can be used to plot a bar chart.

NetRevenueRetentionRate = 
VAR Last12Months = DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -12, MONTH) 
VAR TotalRevenue = CALCULATE(SUM(Sales[Revenue]), Last12Months) 
VAR LostRevenue = CALCULATE(SUM(Sales[Revenue]), LostCustomers, Last12Months) 
RETURN
IF(TotalRevenue > 0, (TotalRevenue - LostRevenue) / TotalRevenue, 0)

NetRevenueRetentionRateByMonth = 
SUMMARIZE(
    DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -12, MONTH),
    'Date'[Month],
    "NRR", 
    VAR TotalRev = CALCULATE(SUM(Sales[Revenue]), 'Date'[Date] >= EDATE(LASTDATE('Date'[Date]), -12) && 'Date'[Date] <= LASTDATE('Date'[Date]))
    VAR LostRev = CALCULATE(SUM(Sales[Revenue]), LostCustomers, 'Date'[Date] >= EDATE(LASTDATE('Date'[Date]), -12) && 'Date'[Date] <= LASTDATE('Date'[Date]))
    RETURN
    IF(TotalRev > 0, (TotalRev - LostRev) / TotalRev, 0)
)

Breakdown of the Extension

• NetRevenueRetentionRateByMonth: This new measure utilizes SUMMARIZE to create a monthly breakdown of the NRR.
• Date Filtering: The DATESINPERIOD function is reused to ensure the calculation stays relevant to the last 12 months, broken down by monthly intervals.
• Nested Variables: Inside the SUMMARIZE, we define TotalRev and LostRev, similar to the original measure but scoped to each month.
• Return Statement: It calculates NRR for each month and collects it for visualization.

Conclusion

This extension integrates seamlessly with the existing measure, allowing for the generation of a bar chart over the NRR per month for the last 12 months. This visualization will enhance reporting and decision-making capabilities related to revenue retention.

For further learning about such data visualization techniques and best practices in DAX, the Enterprise DNA Platform offers valuable resources that can help enhance your skills in data modeling and analytics.