DAX Formula for Calculating Monthly Recurring Revenue (MRR)

Calculating Monthly Recurring Revenue (MRR) with DAX 📊

Monthly Recurring Revenue (MRR) is a crucial metric for subscription-based businesses. It represents the predictable revenue a company expects to receive each month. Here's how to calculate MRR using DAX in Power BI:

Understanding the Data Model 🗂️

Before diving into the DAX formula, ensure your data model includes:

• Subscriptions Table: Contains details about each subscription.
• Date Table: A calendar table linked to your subscriptions.

DAX Formula for MRR 📝

Here’s the DAX formula to calculate MRR:

MRR = 
SUMX (
    FILTER (
        Subscriptions,
        Subscriptions[StartDate] <= MAX ( Dates[Date] ) &&
        (Subscriptions[EndDate] >= MIN ( Dates[Date] ) || ISBLANK ( Subscriptions[EndDate] ))
    ),
    Subscriptions[MonthlyRevenue]
)

Explanation 💡

• SUMX: Iterates over a table and returns the sum of an expression.
• FILTER: Returns a table filtered based on specified conditions.
• Subscriptions[StartDate] <= MAX ( Dates[Date] ): Ensures the subscription started before or on the last day of the current month.
• Subscriptions[EndDate] >= MIN ( Dates[Date] ) || ISBLANK ( Subscriptions[EndDate] ): Ensures the subscription either ends after the first day of the current month or has no end date (i.e., is ongoing).
• Subscriptions[MonthlyRevenue]: The monthly revenue for each subscription.

Step-by-Step Implementation ⚙️

1. Open Power BI Desktop.
2. Load your Subscriptions and Date tables.
3. Create a new measure.
4. Paste the DAX formula into the measure.
5. Add the measure to a visual (e.g., a line chart with Dates on the X-axis).

Example 📊

Suppose you have a Subscriptions table with columns like StartDate, EndDate, and MonthlyRevenue. The DAX formula will calculate the sum of MonthlyRevenue for all active subscriptions in each month.

Additional Tips 📌

• Handling Cancellations: Ensure your EndDate is correctly populated when a subscription is cancelled.
• New vs. Existing MRR: You can modify the formula to calculate new MRR by filtering subscriptions that started in the current month.