Financial Analysis and Reporting: Process and Best Practices

Financial analysis and reporting means turning a company's financial data into KPIs like gross margin, working capital, and DSO, then presenting them to whoever has to act on them, from a department head to the board. In Power BI, that means building a model on the general ledger and defining each KPI as a measure once, rather than recalculating it in a spreadsheet every time someone asks for it. This guide assumes you already have a general ledger table, and an accounts receivable table for DSO, loaded into your Power BI model.

The metrics a financial report tracks

• Gross Margin. Revenue minus cost of goods sold, usually shown as a percentage of revenue.

• Working Capital. Current assets minus current liabilities, a measure of short-term liquidity.

• Days Sales Outstanding (DSO). The average number of days it takes to collect payment after a sale.

• Operating Expense. Costs outside cost of goods sold, such as sales, marketing, and admin.

• EBITDA Margin. Earnings before interest, tax, depreciation, and amortization, as a percentage of revenue.

Each of these answers a different question: gross margin asks whether the core business is profitable, working capital and DSO ask whether cash is coming in fast enough, and EBITDA margin asks how the business performs before financing and accounting choices are layered on.

The data model

Most of this comes from the general ledger, plus an accounts receivable table for DSO:

The AccountType and AccountGroup columns on the Chart of Accounts table do the real work. Tagging each account once as Revenue, COGS, Opex, Current Asset, Current Liability and so on means every measure below can filter on that tag instead of hardcoding a list of account codes that will need updating the next time the chart of accounts changes.

Core measures in DAX

Revenue =
CALCULATE(
    SUM('General Ledger'[Amount]),
    'Chart of Accounts'[AccountType] = "Revenue"
)

COGS =
CALCULATE(
    SUM('General Ledger'[Amount]),
    'Chart of Accounts'[AccountType] = "COGS"
)

Gross Margin % =
DIVIDE([Revenue] - [COGS], [Revenue])

Working Capital =
VAR PeriodEnd = MAX('Date'[Date])
RETURN
CALCULATE(
    SUM('General Ledger'[Amount]),
    'Chart of Accounts'[AccountType] = "Current Asset",
    REMOVEFILTERS('Date'),
    'Date'[Date] <= PeriodEnd
) -
CALCULATE(
    SUM('General Ledger'[Amount]),
    'Chart of Accounts'[AccountType] = "Current Liability",
    REMOVEFILTERS('Date'),
    'Date'[Date] <= PeriodEnd
)

DSO =
VAR PeriodStart = MIN('Date'[Date])
VAR PeriodEnd = MAX('Date'[Date])
VAR PeriodDays = DATEDIFF(PeriodStart, PeriodEnd, DAY) + 1
VAR ReceivablesAtEnd =
    CALCULATE(
        SUM('Accounts Receivable'[Amount]),
        REMOVEFILTERS('Date'),
        'Accounts Receivable'[InvoiceDate] <= PeriodEnd,
        OR(
            ISBLANK('Accounts Receivable'[PaidDate]),
            'Accounts Receivable'[PaidDate] > PeriodEnd
        )
    )
RETURN
DIVIDE(ReceivablesAtEnd, [Revenue]) * PeriodDays

Two things in these measures are easy to get wrong:

• Working capital is a balance, not a movement. It adds every entry up to the end of the selected period, not only the entries inside it. That is why the measure removes the date filter and then keeps everything on or before the period end.

• DSO depends on the period length. It divides receivables still open at period end by revenue in the period, then multiplies by the number of days. A one-month and a one-quarter selection therefore give different answers. If readers need a stable number, fix it to a standard window such as the last 90 days.

The measures also assume one sign convention in Amount, such as revenue stored as a positive number. General ledger exports often store credits as negatives, so check yours and flip the sign where needed.

Report layout

A financial report usually splits across three pages, each answering a different question:

1. Profit and loss. A matrix with the account hierarchy on rows and month on columns, plus cards for Revenue, Gross Margin %, and EBITDA Margin.

2. Balance sheet and working capital. Current assets and liabilities by category, a trend of Working Capital over time, and the DSO measure next to it.

3. Cash flow. Cash generated by operations versus cash used in financing and investing, usually as a waterfall so the reader can see where cash entered and left.

For the step-by-step build, see our guide to creating a financial report. For how these differ from other report types a finance team produces, see accounting reports: definition and types.

Two practices that carry over from spreadsheet reporting

Two habits carry over from spreadsheet-based reporting and still apply once the report lives in Power BI:

• Match the metric to the industry. Cash flow drivers for a retail business and a software business differ enough that the same dashboard template will not fit both. Build the model around the accounts that actually matter to the business it describes.

• Know who reads the report. A board deck needs three or four headline numbers. A controller reviewing the close needs the full account hierarchy. Building both from one model, with different pages or different report views, is easier than maintaining two separate files.

A sales-specific version of the same idea, applied to revenue and pipeline metrics instead of the P&L, is in our guide to building a sales dashboard.

Sources

• REMOVEFILTERS function (DAX) - Microsoft Learn

• DATEDIFF function (DAX) - Microsoft Learn

• DIVIDE function (DAX) - Microsoft Learn