The Challenge
Financial reporting was fractured. Each business entity maintained its own reporting cadence, account structure, and Excel templates. The finance team spent the first two weeks of every month just reconciling numbers—and still couldn’t guarantee accuracy.
The core issue: There was no canonical definition of revenue, cost, or margin that worked across entities.
Technical Approach
Mapping Layer Design
The solution centered on a robust mapping architecture:
- Source Account (Entity A) → Mapping Table → Unified Account Hierarchy
- Source Account (Entity B) → Mapping Table → Unified Account Hierarchy
- Source Account (Entity C) → Mapping Table → Unified Account Hierarchy
The mapping table became the single point of control for account categorization, hierarchy levels, and entity-specific exceptions.
Key DAX Patterns
Consolidated P&L with entity filtering:
Net Revenue =
CALCULATE(
SUM(Fact_GL[Amount]),
Dim_Account[Category] = "Revenue"
) -
CALCULATE(
SUM(Fact_GL[Amount]),
Dim_Account[Category] = "Returns & Allowances"
)
Results
| Metric | Before | After |
|---|---|---|
| Reports to check for P&L | 20+ | 1 |
| Monthly close reporting time | 2 weeks | 2 days |
| Cross-entity comparison | Manual/impossible | Native filtering |
What I’d Do Differently
With hindsight and fewer constraints:
- Backend-first approach with proper SQL-based ETL
- Implement incremental refresh with partitioning
- Add calculation groups for time intelligence
