Milestone Project 2: B2B SaaS Financial & Retention DAX Metrics Engine

Target Role: Senior BI Developer / DAX Specialist
Estimated Time: 200 min (3.3 Hours)
Deliverable Format: Power BI Project (.pbip Git repository with TMDL) or .pbix

1. Business Context & Problem Statement

You are the Lead BI Developer for "CloudPulse SaaS," a B2B subscription platform. Leadership needs an executive subscription analytics model to monitor Annual Recurring Revenue (ARR), Monthly Recurring Revenue (MRR), subscriber expansion, contraction, customer churn, and customer lifetime value (LTV).

2. Technical Deliverable Specifications

1. Core Measure Library (_Measures_Core):
   • [Total MRR]: Active recurring revenue at current period.
   • [Total ARR]: [Total MRR] * 12.
   • [Active Subscriptions]: Distinct count of active subscriptions.
2. Time Intelligence & Rolling Windows (_Measures_TimeIntelligence):
   • [MRR Prior Month]: Evaluated via DATEADD or PARALLELPERIOD.
   • [MRR MoM Growth %]: Safe percentage variance using DIVIDE().
   • [MRR 3-Month Trailing Moving Average]: Computed via DATESINPERIOD.
   • [YTD Revenue]: Cumulative year-to-date calculation.
3. Retention & Cohort DAX (_Measures_Retention):
   • [Logo Churn Rate %]: Cancelled subscriptions divided by starting active subscriptions.
   • [Net Revenue Retention % (NRR)]: Scaffolded modular template:

     [Net Revenue Retention %] = 
     VAR StartingMRR = ...
     VAR ExpansionMRR = ...
     VAR ContractionMRR = ...
     VAR ChurnMRR = ...
     RETURN
         DIVIDE(StartingMRR + ExpansionMRR - ContractionMRR - ChurnMRR, StartingMRR)
     

4. Enterprise Bonus: Calculation Group:
   • Author a Time Intelligence Calculation Group (Current, MoM, MoM %, YTD, 3M Moving Avg) using SELECTEDMEASURE() to eliminate measure sprawl.

3. Grading Rubric (100 Points Total)

• DAX Precision & Mathematical Correctness (35 pts): Proper CALCULATE, filter modifiers, and period shifting.
• Context Transition & Iterator Hygiene (25 pts): Zero row-by-row context transition bottlenecks.
• Measure Group Cleanliness (20 pts): Organized measure tables with descriptive names and formatted DAX.
• Edge Case & Division Safety (20 pts): Safe handling of division by zero, leap years, and missing dates.