Power Pivot Multi-Currency Consolidated P&L Model
Seed: Fact table (date, entity, GL, amount, currency), Rates table; DAX measure example: =SUMX(VALUES(Fact[Amount]),Fact[Amount]*RELATED(Rates[Rate]))ADVERTISEMENT - IN-ARTICLE
Implementation Guide
Uses Power Pivot and DAX to build a consolidated P&L in reporting currency with time-sensitive FX rates, cross-entity eliminations, and drillable measures by region and product. Supports revaluation using historical vs spot rates, and reporting at monthly/quarterly levels. Includes KPI cards, variance drivers and aggregation logic that keeps source transactions detailed for audit. Ideal for finance teams handling multi-entity reporting without heavy BI investments.
💡 Expert Q&A Insights
Q: \
How to handle different reporting currencies?\" \"
Q: Store a rate table keyed by currency and reporting date and use DAX measures to convert amounts at the needed granularity.\"\n\"
Can DAX handle revaluation journal entries?\" \"