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

# 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: ```dax [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.