Financial Reporting in Power BI: P&L, Balance Sheet, Cash Flow

CFOs who replaced static Excel financial packages with Power BI dashboards report 60-80% reduction in month-end close reporting time. The reason: instead of manually assembling P&L tables, formatting balance sheets, and emailing PDFs, they publish once and every stakeholder — board members, department heads, investors — sees live data filtered to their relevant view.

Building financial statements in Power BI is architecturally different from building sales or operational dashboards. Financial statements require precise account mapping, period comparisons down to the cent, and audit-trail transparency. This guide shows the complete implementation: data model, account classification, P&L structure, balance sheet visualization, cash flow waterfall, and the DAX time intelligence formulas that make it all work.

Key Takeaways

• Financial reporting requires a GL transaction-level data model (not pre-aggregated report extracts)
• Account classification table maps chart of accounts to P&L / Balance Sheet / Cash Flow categories
• Matrix visual with calculated columns is the best Power BI visual for statement format
• Time intelligence: DATESYTD, SAMEPERIODLASTYEAR, and PARALLELPERIOD handle all period comparisons
• Cash flow statement uses the indirect method: Net Income → Working Capital adjustments → Operating CF
• Variance analysis measures (actual vs budget) are essential for management reporting
• Row-level security on subsidiary dimension controls which entities each reader sees
• Paginated reports (Power BI Report Builder) export pixel-perfect statements to PDF for board packs

---

Data Model for Financial Reporting

Core Tables

GL_Transactions (fact table — one row per journal entry line):

Chart_of_Accounts (dimension):

Budget (optional fact table):

---

Profit & Loss Statement Structure

Account Classification for P&L

The P&L section structure should map your chart of accounts to standard P&L categories:

Revenue
  - Product Revenue
  - Service Revenue
  - Other Revenue
Cost of Goods Sold
  - Product COGS
  - Direct Labor
  - Manufacturing Overhead
= Gross Profit
Operating Expenses
  - Sales & Marketing
  - General & Administrative
  - Research & Development
  - Depreciation & Amortization
= EBITDA / Operating Income
Other Income / Expense
  - Interest Income
  - Interest Expense
  - Foreign Exchange
= Net Income Before Tax
  - Income Tax Expense
= Net Income

DAX Measures for P&L

// Core GL amount measure (handles sign convention)
GL Amount =
SUMX(
    GL_Transactions,
    SWITCH(
        RELATED(Chart_of_Accounts[AccountType]),
        "Revenue", GL_Transactions[CreditAmount] - GL_Transactions[DebitAmount],
        "Expense", GL_Transactions[DebitAmount] - GL_Transactions[CreditAmount],
        "Asset", GL_Transactions[DebitAmount] - GL_Transactions[CreditAmount],
        "Liability", GL_Transactions[CreditAmount] - GL_Transactions[DebitAmount],
        "Equity", GL_Transactions[CreditAmount] - GL_Transactions[DebitAmount],
        GL_Transactions[NetAmount]
    )
)

// Revenue
Total Revenue =
CALCULATE([GL Amount], Chart_of_Accounts[PLSection] = "Revenue")

// COGS
Total COGS =
CALCULATE([GL Amount], Chart_of_Accounts[PLSection] = "COGS")

// Gross Profit
Gross Profit = [Total Revenue] - [Total COGS]

// Gross Margin %
Gross Margin % = DIVIDE([Gross Profit], [Total Revenue], 0)

// Operating Expenses
Total OpEx =
CALCULATE([GL Amount], Chart_of_Accounts[PLSection] = "Operating Expense")

// EBITDA
EBITDA = [Gross Profit] - [Total OpEx]

// D&A (addback for EBITDA from EBIT)
DA Addback =
CALCULATE(
    [GL Amount],
    Chart_of_Accounts[AccountName] IN {"Depreciation", "Amortization"}
)

// Net Income
Net Income =
[EBITDA] +
CALCULATE([GL Amount], Chart_of_Accounts[PLSection] = "Other Income/Expense") -
CALCULATE([GL Amount], Chart_of_Accounts[PLSection] = "Tax Expense")

P&L Time Intelligence

// Prior Year same period
PY Revenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Date[Date]))

// YTD Revenue
YTD Revenue = CALCULATE([Total Revenue], DATESYTD(Date[Date]))

// Prior YTD Revenue
PY YTD Revenue = CALCULATE([YTD Revenue], SAMEPERIODLASTYEAR(Date[Date]))

// Budget Variance
Revenue Budget Variance =
[Total Revenue] -
CALCULATE(SUM(Budget[BudgetAmount]), Budget[ScenarioID] = "Budget")

// Budget Variance %
Revenue Budget Var % =
DIVIDE([Revenue Budget Variance],
    CALCULATE(SUM(Budget[BudgetAmount]), Budget[ScenarioID] = "Budget"), 0)

// Rolling 12-Month Revenue
R12M Revenue =
CALCULATE(
    [Total Revenue],
    DATESINPERIOD(Date[Date], LASTDATE(Date[Date]), -12, MONTH)
)

P&L Matrix Visual Design

The recommended visual for a P&L statement is a Matrix:

• Rows: P&L line items (from Chart_of_Accounts[PLSection] with subtotals)
• Columns: Periods (months, quarters, YTD)
• Values: Actual amount, Budget amount, Variance $, Variance %

Configure the Matrix:

1. Enable subtotals at the section level (Revenue total, COGS total, etc.)
2. Use conditional formatting: Red = unfavorable variance, Green = favorable
3. Apply custom sort order via Chart_of_Accounts[SortOrder]
4. Format numbers: £#,##0K for thousands, 0.0% for percentages

---

Balance Sheet Visualization

Balance Sheet Structure

Assets
  Current Assets
    - Cash and Equivalents
    - Accounts Receivable
    - Inventory
    - Prepaid Expenses
  Fixed Assets
    - Property, Plant & Equipment (net)
    - Intangible Assets
    - Goodwill
= Total Assets

Liabilities
  Current Liabilities
    - Accounts Payable
    - Accrued Expenses
    - Deferred Revenue
    - Short-term Debt
  Long-term Liabilities
    - Long-term Debt
    - Deferred Tax
= Total Liabilities

Equity
  - Common Stock
  - Retained Earnings
  - Additional Paid-In Capital
= Total Equity
= Total Liabilities + Equity

Balance Sheet DAX Measures

Balance sheet measures use balance (cumulative) rather than period amounts — all transactions from inception to the selected date:

// Balance Sheet uses cumulative amounts (not period totals)
BS Amount =
CALCULATE(
    [GL Amount],
    FILTER(
        ALL(Date),
        Date[Date] <= MAX(Date[Date])
    )
)

// Total Current Assets
Current Assets =
CALCULATE([BS Amount], Chart_of_Accounts[BSSection] = "Current Assets")

// Total Fixed Assets (net of depreciation)
Fixed Assets =
CALCULATE([BS Amount], Chart_of_Accounts[BSSection] = "Fixed Assets")

// Total Assets
Total Assets = [Current Assets] + [Fixed Assets]

// Total Current Liabilities
Current Liabilities =
CALCULATE([BS Amount], Chart_of_Accounts[BSSection] = "Current Liabilities")

// Total Equity
Total Equity =
CALCULATE([BS Amount], Chart_of_Accounts[BSSection] = "Equity")

// Balance Sheet Check (must equal 0 for a balanced BS)
BS Balance Check =
[Total Assets] - ([Current Liabilities] +
    CALCULATE([BS Amount], Chart_of_Accounts[BSSection] = "Long-term Liabilities") +
    [Total Equity])

// Key Ratios
Current Ratio = DIVIDE([Current Assets], [Current Liabilities], 0)
Quick Ratio =
DIVIDE(
    [Current Assets] -
    CALCULATE([BS Amount], Chart_of_Accounts[AccountSubType] = "Inventory"),
    [Current Liabilities],
    0
)
Debt to Equity = DIVIDE([Current Liabilities], [Total Equity], 0)

Balance Sheet Visualization Pattern

Use a combination of visuals:

1. Matrix for the full balance sheet table (same structure as P&L matrix)
2. Stacked bar chart for Assets vs Liabilities + Equity (trend over 12 months)
3. KPI cards for Current Ratio, Quick Ratio, Debt/Equity
4. Gauge for Working Capital vs target

---

Cash Flow Statement (Indirect Method)

The indirect method starts with Net Income and adjusts for non-cash items and working capital changes.

Operating Activities:
  + Net Income
  + Depreciation & Amortization
  + / - Changes in Working Capital:
    + Decrease / - Increase in Accounts Receivable
    + Decrease / - Increase in Inventory
    - Decrease / + Increase in Accounts Payable
    - Decrease / + Increase in Accrued Expenses
= Net Cash from Operations

Investing Activities:
  - Capital Expenditures (PP&E additions)
  + Proceeds from Asset Sales
= Net Cash from Investing

Financing Activities:
  + Debt Proceeds
  - Debt Repayments
  - Dividends Paid
  + Equity Raised
= Net Cash from Financing

= Net Change in Cash
+ Beginning Cash Balance
= Ending Cash Balance

Cash Flow DAX Measures

// Period-over-Period change in Accounts Receivable
AR Change =
VAR CurrentAR = CALCULATE([BS Amount], Chart_of_Accounts[AccountSubType] = "Accounts Receivable")
VAR PriorAR = CALCULATE(
    [BS Amount],
    Chart_of_Accounts[AccountSubType] = "Accounts Receivable",
    DATEADD(Date[Date], -1, MONTH)
)
RETURN PriorAR - CurrentAR  // Decrease in AR = positive cash impact

// Change in Inventory
Inventory Change =
VAR CurrentInv = CALCULATE([BS Amount], Chart_of_Accounts[AccountSubType] = "Inventory")
VAR PriorInv = CALCULATE([BS Amount], Chart_of_Accounts[AccountSubType] = "Inventory",
    DATEADD(Date[Date], -1, MONTH))
RETURN PriorInv - CurrentInv

// Capital Expenditures (negative — cash outflow)
CapEx =
-CALCULATE(
    [GL Amount],
    Chart_of_Accounts[AccountSubType] = "Fixed Asset Addition",
    Chart_of_Accounts[CFSection] = "Investing"
)

// Net Cash from Operations
Operating Cash Flow =
[Net Income] +
[DA Addback] +
[AR Change] +
[Inventory Change] +
CALCULATE([GL Amount], Chart_of_Accounts[AccountSubType] = "Accounts Payable Change")

// Free Cash Flow
Free Cash Flow = [Operating Cash Flow] + [CapEx]

Cash Flow Waterfall Chart

The waterfall chart is the ideal visual for cash flow:

1. Create a calculated table with the cash flow line items and their amounts
2. Use the waterfall chart visual (built into Power BI)
3. Configure "Increase" and "Decrease" categories to show the contribution of each item
4. Mark "Net Cash from Operations", "Net Cash from Investing", "Net Cash from Financing" as subtotals
5. Mark "Ending Cash Balance" as a total

---

Variance Analysis: Actual vs Budget vs Forecast

// Scenario selector (works with a scenario slicer)
Selected Measure =
SWITCH(
    SELECTEDVALUE(Scenario[Scenario]),
    "Budget", SUM(Budget[Amount]),
    "Forecast", CALCULATE(SUM(Budget[Amount]), Budget[ScenarioID] = "Q2 Forecast"),
    [Total Revenue]  -- default to Actual
)

// Traffic light variance
Variance Status =
VAR Var% = [Revenue Budget Var %]
RETURN
SWITCH(TRUE(),
    Var% >= 0.05, "Favorable",
    Var% >= -0.05, "On Track",
    "Unfavorable"
)

---

Frequently Asked Questions

Can Power BI replace our existing ERP financial reporting module?

Power BI can replace most standard financial reports (P&L, Balance Sheet, Cash Flow, AR Aging) with more flexible, interactive equivalents. However, statutory financial statements (GAAP/IFRS certified), audit-ready reports with signature controls, and transactional posting functions remain in the ERP. The optimal approach: use ERP for transactional control and compliance, Power BI for management reporting and board-level analytics.

How do I ensure my P&L in Power BI matches the ERP exactly?

The most common reconciliation issue is sign conventions — ERPs define whether revenue accounts have credit or debit normal balances, and Power BI must apply the same logic. Build a test measure that sums all GL entries (debits + credits) and verify it equals zero for a closed period. Then compare your P&L totals against the ERP's native P&L report period by period until they match.

Can multiple subsidiaries be consolidated in one Power BI financial report?

Yes — this is one of Power BI's major advantages over many ERP financial reporting modules. Import GL data from all subsidiaries with a SubsidiaryID column, then build consolidation measures that sum across subsidiaries. For intercompany eliminations, flag intercompany accounts in the Chart of Accounts and exclude them from consolidated measures. Currency translation requires an exchange rate table joined to the transaction date.

How do I create board-pack quality PDF reports from Power BI?

Power BI Report Builder (paginated reports) creates pixel-perfect PDF output — professional formatting, page breaks, headers/footers, and precise table layouts. This is separate from Power BI Desktop but uses the same datasets. For board packs, build the management commentary in Word, generate the financial tables via paginated reports, and combine via Power Automate on a monthly schedule. Power BI Premium or PPU license is required for paginated reports.

What is the best way to handle fiscal years that don't match calendar years?

Create a custom date table in DAX or Power Query that includes a FiscalYear, FiscalQuarter, and FiscalPeriod column based on your fiscal year start month. Mark this table as a Date Table in Power BI. Use DATESYTD with the fiscal year end date parameter: CALCULATE([Revenue], DATESYTD(Date[Date], "3/31")) for a March fiscal year end. All time intelligence functions accept this optional year-end parameter.

---

Next Steps

Financial reporting in Power BI requires more careful data modeling than operational dashboards — account sign conventions, balance sheet cumulative logic, and cash flow indirect method calculations all need precise implementation. When done correctly, the result is a CFO dashboard that replaces weeks of Excel work with minutes of exploration.

ECOSIRE combines Power BI expertise with deep accounting and finance knowledge. Our Power BI dashboard development services include complete financial statement design and our accounting services cover the data quality and chart-of-accounts structuring that makes financial dashboards reliable.

Contact our finance analytics team to discuss your financial reporting requirements and design a Power BI architecture that gives your leadership team the financial visibility they need.