Power BI Multi-Entity Consolidation Reporting for D365

Power BI as the Consolidation Reporting Layer

Multi-entity portfolios have a challenge: each entity runs its own D365 instance (or legacy ERP), with separate GL. Consolidation via SQL scripts or accounting software (Prophix, Vena) is traditional but brittle.

Power BI inverts this: instead of ETL pulling data from each system into a central warehouse, Power BI is the central reporting hub. It connects to all entity D365 instances, models the GL data, applies consolidation logic, & publishes unified reports.

Architecture:

• Data Sources: Multiple D365 instances (Entity A GL, Entity B GL, etc.), GL dimension tables (account master, cost center master), currency conversion rates (cached).
• Data Model: One fact table (GL consolidated), many dimensions (entity, account, period, currency). Relationships enable filtering & roll-ups.
• Calculations: Measures for EBITDA, FCF, variance, eliminations (DAX).
• Reporting: Executive dashboards (consolidated P&L, variance, KPIs), detail reports (account-level, entity-level), compliance reports (paginated).

Advantages Over Traditional Consolidation Software:

• Real-time or near-real-time data (vs monthly cutoff).
• Seamless integration with D365 (no export/import cycle).
• Interactive dashboards (drill-down, filter by entity, period, account).
• Self-service analytics for power users; reduces burden on finance team.
• Scalable (add new entity to Power BI model, no major reconfiguration).

Connecting to Multiple D365 Instances

Scenario: Parent company + 3 subsidiaries, each with its own D365 FO instance. Each instance has a GL table (GeneralJournalEntry).

Connection Methods:

1. OData Connector (Recommended):

• D365 exposes OData endpoints: https://entityA.dynamics.com/data/GeneralJournalEntries.
• Power Query connects to OData endpoint, filters for GL accounts, pulls data.
• Repeat for each entity: Entity A, B, C have separate queries.
• Append queries: combine all three into a single GL fact table with Entity column.

2. Power Query (Direct Connectivity):

• Each entity exports GL via Synapse Link for Dataverse (the current successor — Export to Data Lake / Data Export Service were deprecated and decommissioned beginning March 2025), Fabric link, or manual CSV export.
• Power Query reads files from ADLS or SharePoint, transforms, appends.
• Pro: flexibility; Con: latency (depends on export frequency).

3. Data Lake Approach (Recommended for Scale):

• Each D365 instance exports GL to Azure Data Lake (Parquet, Delta format).
• Power BI reads directly from Data Lake, single source of truth.
• Pro: scales to 100+ entities; Con: requires Data Lake setup.

Authentication: D365 instances must be connected via service principal or user credentials. In Power BI Premium, on-premises gateway can securely forward credentials to each instance.

Financial Consolidation Data Model

Fact Table (GL):

Column	Type	Purpose
EntityKey	String	Foreign key to Entity dimension
AccountKey	String	Foreign key to Account dimension
PeriodKey	String (YYYY-MM)	Year-Month; supports trend analysis
CurrencyCode	String	USD, EUR, GBP, etc.
Amount	Decimal	Transactional amount in source currency
AmountUSD	Decimal	Converted to USD (if different from source)
IsIntercompany	Boolean	True if transaction is entity-to-entity; used for elimination
CounterpartyEntity	String	The other entity in intercompany transaction

Dimension Tables:

• Entity: EntityKey, EntityName, Region, Subsidiary Y/N, OwnershipPct (for minority interest).
• Account: AccountKey, AccountNumber, AccountName, Category (Asset/Liability/Equity/Revenue/Expense), ConsolidationGroup (e.g., “Intercompany AP”).
• Period: PeriodKey, Year, Month, Quarter, IsActual/Budget.
• Currency: CurrencyCode, CurrencyName, ConversionRateToUSD (for fixed conversion) or external conversion table.

Consolidation Dimension (Optional):

• ConsolidationType: “Actual”, “Elimination”, “Consolidation Adjustment”.
• Allows switching between consolidated & pre-consolidated view.

DirectQuery vs Import Mode for D365 Data

Two connection modes, different trade-offs:

Aspect	Import Mode	DirectQuery Mode
Data Location	Copied to Power BI; cached	Stays in D365; queried on-demand
Freshness	Stale (refresh interval, e.g., hourly, daily)	Real-time
Performance	Fast (in-memory calculation)	Slower (network latency + D365 API latency)
Data Volume	Limited by license: ~1 GB per dataset on Pro/shared capacity; up to 25 GB on Premium F64/P1; larger with higher SKUs	Unlimited (depends on D365 API limits)
Calculations	All DAX calculations supported	Limited DAX (some functions not supported in DirectQuery)
Use Cases	Historical analysis, trend, variance reports	Operational dashboards, real-time KPIs

Recommendation for Consolidation: Use Import mode. Consolidation reporting is typically monthly/quarterly; daily refresh is acceptable. Import mode allows complex DAX calculations (eliminations, consolidation adjustments) & better performance for drill-downs. Reserve DirectQuery for operational dashboards (AP aging, AR aging).

Refresh Strategy: Schedule incremental refresh (only fetch new/changed rows since last refresh) to reduce load & refresh time. For large GL tables (>1M rows), incremental refresh reduces import time from 2 hours to 15 minutes.

Multi-Currency & Conversion Logic

Entities operate in different currencies. Consolidated reporting requires conversion to a reporting currency (typically USD or EUR).

Conversion Approaches:

1. Spot Rate (Transaction Date):

• Each GL transaction has a conversion rate (rate on transaction date).
• D365 OData includes ConversionRate field; Power BI uses it directly.
• Pro: accuracy; Con: requires rate to be stored in D365 at time of transaction.

2. Average Rate (Period):

• For P&L accounts (revenue, expense), use monthly average exchange rate.
• Lookup: use LOOKUPVALUE in DAX to fetch average rate for (Currency, Period).
• Pro: Standard consolidation practice; Con: requires external rate table.

3. Year-End/Month-End Rate (Balance Sheet):

• For balance sheet accounts (assets, liabilities, equity), use rate at period-end (month-end, year-end).
• DAX calculation: IF account is balance sheet, use month-end rate; IF P&L, use average rate.

Implementation in Power BI:

// DAX measure: Converted Amount
Measure AmountConverted =
  VAR CurrCode = SELECTEDVALUE(GL[CurrencyCode])
  VAR PeriodVal = SELECTEDVALUE(GL[PeriodKey])
  VAR AccountCat = SELECTEDVALUE(Account[Category])
  VAR ConversionRate =
    IF(
      AccountCat IN {"Asset", "Liability", "Equity"},
      // Balance sheet: month-end rate
      LOOKUPVALUE(
        ExchangeRate[Rate],
        ExchangeRate[Currency], CurrCode,
        ExchangeRate[Period], PeriodVal,
        ExchangeRate[RateType], "Month-End"
      ),
      // P&L: average rate
      LOOKUPVALUE(
        ExchangeRate[Rate],
        ExchangeRate[Currency], CurrCode,
        ExchangeRate[Period], PeriodVal,
        ExchangeRate[RateType], "Average"
      )
    )
  RETURN
    SUM(GL[Amount]) * ConversionRate
      

Exchange Rate Table: Maintain external table (daily rates from Fed, ECB, or Bloomberg API). D365 integration: nightly job fetches rates & stores in GL table or separate ExchangeRate table in Power BI.

Intercompany Eliminations & Consolidation Adjustments

Challenge: Entity A sells $100K to Entity B. Entity A records revenue; Entity B records COGS. In consolidated view, both should be eliminated (no external sale occurred).

Solution: Use DAX measures to identify & reverse intercompany transactions.

1. Flag Intercompany Transactions:

• D365 GL includes IsIntercompany flag or CounterpartyEntity column.
• If not available, infer: GL account is “Intercompany AP” or “Intercompany AR”.

2. Create Elimination Measures:

// Measure: Consolidated Amount (including eliminations)
Measure AmountConsolidated =
  VAR TotalAmount = SUM(GL[Amount])
  VAR IntercompanyAmount =
    CALCULATE(
      SUM(GL[Amount]),
      FILTER(GL, GL[IsIntercompany] = TRUE)
    )
  RETURN
    TotalAmount - IntercompanyAmount
      

3. Consolidation Adjustments:

• Minority Interest: if Entity has 80% ownership, 20% is minority. Adjust retained earnings & net income.
• Consolidation Entries: goodwill impairment, consolidation basis adjustments (acquisition price allocation).
• Intercompany Profit: if Entity A sells inventory to B at 20% markup, defer the profit in consolidation.

DAX Example: Minority Interest Adjustment

// Measure: Net Income (after minority interest adjustment)
Measure NetIncomeConsolidated =
  VAR NetIncome = [AmountConsolidated]
  VAR EntityOwnership =
    LOOKUPVALUE(
      Entity[OwnershipPct],
      Entity[EntityKey], SELECTEDVALUE(GL[EntityKey])
    )
  VAR MinorityInterest = NetIncome * (1 - EntityOwnership)
  RETURN
    NetIncome - MinorityInterest
      

KPI Standardization Across Entities

Consolidated dashboards require standardized KPIs: EBITDA, free cash flow, days sales outstanding, etc.

EBITDA Calculation:

// Measure: EBITDA
Measure EBITDA =
  VAR EBIT = [NetIncome] + [InterestExpense] + [TaxExpense]
  VAR Depreciation = CALCULATE(SUM(GL[Amount]), GL[Account] = "Depreciation")
  VAR Amortization = CALCULATE(SUM(GL[Amount]), GL[Account] = "Amortization")
  RETURN
    EBIT + Depreciation + Amortization
      

Days Sales Outstanding (DSO):

• DSO = (AR / Revenue) * 365.
• Standardize across entities despite different collection practices.

Implementation:

• Define each KPI once in Power BI (as a measure).
• Reference the measure in all reports.
• Change in definition flows to all reports (single source of truth).

Row-Level Security & Data Access Control

In a multi-entity portfolio, Entity A CFO should see Entity A data only; CEO sees consolidated.

RLS Implementation:

1. Create a User-Entity mapping table in Power BI or D365.
2. Define RLS rules: IF User[Email] = CFO of Entity A, filter GL[Entity] = “Entity A”.
3. Publish to Power BI; users see only their authorized data.

Example RLS Rule (DAX):

// RLS rule on GL table
GL[EntityKey] = LOOKUPVALUE(
  UserEntity[AuthorizedEntity],
  UserEntity[UserEmail], USERPRINCIPALNAME()
)
      

Roles in Power BI:

• CFO Role: Sees own entity + consolidated (parent company) reporting.
• Controller Role: Sees all entities (for consolidation & audit).
• PE Sponsor Role: Sees consolidated only (portfolio-level view).

Testing RLS: In Power BI Desktop, use “View As” to test role access. Ensure CFO user sees only their data.

Paginated Reports for Compliance & Audit

Consolidated financial statements for auditors, lenders, regulatory bodies require formal formatting (not interactive dashboards).

Power BI Paginated Reports:

• Fixed layout, print-friendly, suitable for PDF export.
• Includes management assertions, audit notes, footnotes.
• Supports complex hierarchies (GL by account, department, cost center).

Typical Paginated Reports:

• Consolidated Statement of Income: Revenue, COGS, Gross Profit, OpEx, EBITDA, D&A, EBIT, Interest, Taxes, Net Income.
• Consolidated Balance Sheet: Assets (current, fixed), Liabilities (current, long-term), Equity.
• Consolidated Statement of Cash Flows: Operating, investing, financing activities.
• Segment Reporting: P&L by entity, by product line, by region.
• Management Footnotes: Consolidation policy, elimination details, accounting changes.

Power BI Premium vs Pro for Enterprise Consolidation

Power BI Pro:

• $14/month per user (price increased from $10 to $14 effective April 1, 2025).
• Dashboards & reports stored in workspaces.
• Sharing limited to other Pro users or guest (limited access).
• Import data: up to 1 GB per dataset.
• Refresh: up to 8x per day.

Power BI Premium:

• Microsoft Fabric F64 capacity (the current equivalent of the retired Power BI Premium P1 SKU — P-SKUs were removed from new purchases on July 1, 2024; non-EA renewals ended January 1, 2025; EA customers may renew through their EA agreement term). Fabric F64 is purchased as pay-as-you-go Azure capacity or with annual reservations; list pricing varies by region — check the Microsoft Fabric pricing page for current rates. Larger F-SKUs (F128, F256 … F2048) cover the P2–P5 equivalent workloads.
• Shared capacity: unlimited users (both Pro & free users) can access reports without per-user license.
• Incremental refresh: import only new/changed rows, reducing refresh time & load.
• Paginated reports: included.
• XMLA endpoints: connect external tools (Excel, Tableau) directly to Power BI.
• Larger data volumes with higher Fabric SKUs (F256 equivalent to P3 supports 100 GB semantic model memory; F512+ for very large workloads).

Recommendation for Consolidation: Power BI Premium. Reasons:

• Incremental refresh handles large GL tables efficiently.
• Paginated reports are essential for compliance.
• Shared capacity allows executives (non-Pro users) to view dashboards.
• XMLA endpoints enable Excel integration (Power Query directly querying Power BI).

Cost Estimation: A Microsoft Fabric F64 capacity (the equivalent of the retired P1 SKU) is priced as pay-as-you-go Azure capacity — check the Microsoft Fabric pricing page for current regional rates. Annual reservations offer approximately 40% savings vs. pay-as-you-go. F64 supports 25 GB max semantic model memory, up to 50 concurrent DirectQuery connections, and as many reports as capacity allows.

Methodology

This guide synthesizes Power BI best practices from Microsoft documentation, consolidation accounting principles from IFRS/GAAP standards, & real-world multi-entity reporting implementations. Topics cover Power BI as a consolidation reporting platform, multi-instance D365 connectivity, financial data modeling, DirectQuery vs Import trade-offs, multi-currency conversion logic, intercompany elimination mechanics, KPI standardization, row-level security, compliance reporting, & licensing considerations.

Dataset & Sources: Microsoft Power BI documentation on DAX, data modeling, RLS; IFRS Consolidation Standards (IAS 27, IAS 28); accounting consolidation textbooks; enterprise BI implementation case studies.

Analytical Approach: Evaluated Power BI vs dedicated consolidation software on functionality, cost, ease of use, & scalability. Designed multi-entity data model with proper dimensions & facts. Developed DAX formulas for EBITDA, minority interest, intercompany eliminations. Discussed security & compliance requirements. Analyzed Premium vs Pro pricing for enterprise scenarios.

Limitations: This guide covers consolidated financial reporting in Power BI. Advanced topics (rolling forecasts, scenario planning, what-if analysis) are beyond scope. Tax & transfer pricing calculations are jurisdiction-specific. Audit & regulatory requirements vary; consult your auditor on required consolidation adjustments.

Data Currency: Accurate as of March 2026. Power BI features & Premium pricing evolve quarterly. Consult Microsoft Power BI roadmap, 2025–2026 release notes, & current pricing page for latest capabilities & costs.

Sources

• Power BI Pricing — Microsoft Power Platform (Pro $14/user/mo, PPU $24/user/mo)
• Important update to Microsoft Power BI pricing (April 2025 price change)
• Power BI Premium P-SKU retirement timeline — Microsoft Licensing
• What is Power BI Premium? — Microsoft Fabric / Learn (SKU table, F64 = P1 equivalent)
• Microsoft Fabric pricing — Microsoft Azure
• Configure scheduled refresh — Microsoft Learn (Pro: 8/day; Premium/Fabric: 48/day)
• Transition from legacy data integration to Fabric link / Synapse Link — Microsoft Learn (Export to Data Lake deprecated March 2025)

Last validated June 19, 2026.