Downloads

DAX for financial reporting

Model a general ledger for DAX and build income statement, balance sheet, budget variance and ratio measures with correct signs and fiscal calendars.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Model a general ledger so DAX can build income statement and balance sheet measures.
  • Keep signs correct with one amount column and a fiscal calendar.
  • Build budget variance and ratio measures.
  • Check that subtotals foot, and never add closing balances across time.

This guide is for accountants, controllers and finance analysts who build income statements, balance sheets and budget reports in Power BI or Power Pivot. It assumes you know the basics of measures and filter context from DAX fundamentals. It also uses CALCULATE, SUMX and DIVIDE, which are listed in the function reference.

Financial reports have rules that sales reports do not. Debits and credits have signs. Balance sheet numbers are a snapshot at a date, not a total for a period. Subtotals must add up to totals. This guide shows how to get each of those right in DAX, and where the common mistakes come from.

About the tables in this guide

Most of the tables below are illustrative. They follow a simple general ledger design that many small and mid-size companies use, so you can see the pattern. Names such as GL, Account and 'Date' are not from a sample database. The guide says so each time it introduces a table.

The nonprofit sample database on LearnSQL has a real general ledger with the same shape (gl_journal_lines, gl_accounts, fiscal_periods). The SQL for accountants guide queries it directly. To map a real ledger to this model, rename the columns and keep the logic.

Illustrative table Purpose Key columns
GL One row per journal line GL[Date], GL[AccountKey], GL[EntityKey], GL[Amount], GL[Debit], GL[Credit]
Account Chart of accounts Account[AccountKey], Account[AccountNumber], Account[AccountName], Account[AccountType], Account[Parent], Account[Level1] to Account[Level4]
'Date' Calendar with fiscal columns 'Date'[Date], 'Date'[FiscalYear], 'Date'[FiscalPeriod], 'Date'[FiscalQuarter]
Entity Legal entities or cost centers Entity[EntityKey], Entity[EntityName], Entity[Currency]
Budget Budget by account and month Budget[AccountKey], Budget[Month], Budget[Amount]

Use Contoso names only where the model is Contoso (Sales, 'Date', Product). The examples here are generic ledger names.

The chart of accounts as a one-sided hierarchy

A chart of accounts is a tree. Assets, liabilities, equity, revenue and expenses are the top level. Each account rolls up into a group, and each group rolls up into a statement line. The rule that makes reports foot is that every account has exactly one parent. An account that rolls up to two groups is counted twice.

A one-sided hierarchy has one parent per row and no loops. Put the tree in Account as columns:

AccountNumber AccountName AccountType Level1 Level2 Level3
4000 Product sales Revenue Revenue Product revenue Product sales
4100 Service sales Revenue Revenue Service revenue Service sales
5000 Cost of goods Expense Cost of sales Direct cost Cost of goods
6100 Salaries Expense Operating expenses People Salaries

Each level is a text column. You can use the columns as a hierarchy in a matrix (drag Level1 then Level2), and DAX does not need a parent-child structure for this. The parent-child form is covered below, where it matters.

Sign convention: keep one amount column

Accountants think in debits and credits. Power BI sums numbers, so the model has to turn debits and credits into one signed number. Use a single column, GL[Amount], with this rule:

That rule keeps the model honest. Every journal entry sums to zero, and so does the trial balance. You can check that with one measure:

Ledger Check = ROUND(SUM(GL[Amount]), 2)

The result should be zero on the grand total. If it is not, a journal is out of balance or a line is missing from the load. Put this card on the first page of every financial report.

Many reports need the other sign. Revenue is usually shown as a positive number. Flip the sign in the measure, not in the data, so the ledger stays as it was loaded:

Revenue = -CALCULATE(SUM(GL[Amount]), Account[AccountType] = "Revenue")

When you flip signs, say so in the report title or footnote. A reader who sees a positive revenue and a negative expense in the same table will assume a sign error.

A date table with fiscal periods

Balance sheet and income statement measures depend on the calendar. Build a date table that includes the fiscal year, quarter and period. The fiscal year in the example starts in July. Name it by the year it ends, as many firms do: FY2026 runs July 2025 to June 2026.

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2021, 7, 1), DATE(2027, 6, 30)),
    "FiscalYear", "FY" & (YEAR([Date]) + IF(MONTH([Date]) >= 7, 1, 0)),
    "FiscalPeriod", MOD(MONTH([Date]) - 7, 12) + 1,
    "FiscalQuarter", "Q" & (INT(MOD(MONTH([Date]) - 7, 12) / 3) + 1)
)

Mark the table as the date table in Power BI Desktop, and relate 'Date'[Date] to GL[Date]. The fiscal period numbers start at 1 for July, so FiscalPeriod 12 is June. Use FiscalPeriod for sorting month names in visuals, or the names will sort alphabetically.

Watch out

The date table is the most important table in a finance model. If it has gaps or the wrong fiscal year, every period measure will be wrong, and the errors will look plausible.

Income statement measures

Build the statement from a small set of account-type measures. Each one is a filter on Account[AccountType] or on a statement group column. Put the business rules in one place.

Revenue =
-CALCULATE(SUM(GL[Amount]), Account[AccountType] = "Revenue")

Cost of Goods =
CALCULATE(SUM(GL[Amount]), Account[Level1] = "Cost of sales")

Gross Margin = [Revenue] - [Cost of Goods]

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

Operating Expenses =
CALCULATE(SUM(GL[Amount]), Account[Level1] = "Operating expenses")

EBITDA =
[Gross Margin] - [Operating Expenses]
Accounting note

Read the signs carefully. Cost of Goods is a debit balance, so it is positive without a flip. Revenue is a credit balance, so it is flipped to a positive number.

Gross margin is revenue minus cost, which is correct because both are positive now.

Gross Margin % uses DIVIDE, not /. When there is no revenue in a period, DIVIDE returns blank instead of an error, and the visual shows nothing rather than a broken number.

Why the EBITDA measure filters by group

EBITDA is not a single account. It is revenue minus cost of sales minus operating expenses, before depreciation, amortization, interest and tax. The clean way is to tag each account with a statement group and sum the groups, because then the statement and the measure use the same rule.

Add a column to Account: StatementGroup, with values such as Revenue, Cost of sales, Operating expenses, Depreciation, Interest, Tax. Then:

EBITDA =
-CALCULATE(
    SUM(GL[Amount]),
    Account[StatementGroup] IN { "Revenue", "Cost of sales", "Operating expenses" }
)

Here the sign is flipped once, for the whole set. In the ledger, revenue lines are negative and cost lines are positive. Their sum is therefore the negative of profit before depreciation, and the leading minus sign turns it back into a positive profit. This only works when every group in the list has the normal sign for its type. If a group with a reversed sign is added to the list, the result still looks reasonable and is wrong. Test EBITDA against a manual sum for one month before you trust it.

A safer pattern is to build EBITDA from the measures you already check: [Revenue] - [Cost of Goods] - [Operating Expenses]. It is longer, but each step can be reconciled to the statement.

Showing expenses as negatives

Some reports show costs in parentheses, as a negative. Flip costs for display only:

Cost of Goods (display) = -[Cost of Goods]

Do not use the display measure in other calculations. Keep the positive measure as the base, so Gross Margin is not accidentally computed on negative costs.

Parent-child hierarchies and why we flatten them

Some ledgers store the chart of accounts as a parent-child table, where each account has a ParentAccountKey that points to another account. The DAX functions PATH, PATHITEM and PATHCONTAINS read that structure directly. Here is how it works.

Account Path = PATH(Account[AccountKey], Account[ParentKey])

PATH returns a string such as 1000|1100|1110 for a cash account under a bank group under current assets. PATHITEM(path, 2, INTEGER) returns the second item.

We recommend flattening the hierarchy into Level1 to Level4 columns, as in the table above, for four reasons:

  1. Reports need fixed levels. Matrix visuals and the rest of the report expect named levels. A variable-depth path needs extra work for every level.
  2. Flattening is done once. The path logic runs at refresh time in Power Query or in the source. The report then uses plain columns, which are faster and easier to test.
  3. Unbalanced trees show up at load time. A flattened table with an empty level, or an account with two parents, is easy to spot in a table visual. A path measure hides the problem.
  4. Auditors can read it. A flat list of accounts with their group names is easy to check against the chart of accounts that the controller signed.

Use PATHCONTAINS only when you need to test whether an account sits under a given group and the depth varies. In most finance models that is not needed.

Period comparisons

Comparisons are where most financial reports break. The four common ones are month, year to date, prior year and budget variance.

Month and year to date

Revenue MTD = TOTALMTD([Revenue], 'Date'[Date])

Revenue YTD =
CALCULATE(
    [Revenue],
    DATESYTD('Date'[Date], "6/30")
)
Watch out

DATESYTD with "6/30" ends the year on June 30, which matches a July-to-June fiscal year. Without the year-end argument, the function assumes a calendar year, and every fiscal year-to-date number is wrong by six months.

TOTALMTD and DATESMTD work the same way for months. Use CALCULATE with DATESYTD when you need to combine the period with other filters.

Prior year and growth

Revenue PY =
CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))

Revenue Growth % =
DIVIDE([Revenue] - [Revenue PY], [Revenue PY])

SAMEPERIODLASTYEAR moves the date filter back one year. For a partial year, compare YTD to prior year YTD, not the full prior year to this year to date. The DAX deep end guide covers the traps.

Budget variance with favorable and unfavorable flags

Budget and actual signs must match before you subtract. Budgets are often entered as positive numbers for both revenue and expense. If the budget table uses a different sign from the ledger, the variance will be wrong on the expense lines.

Budget Amount = SUM(Budget[Amount])

Variance = [Revenue] - [Budget Amount]

Variance Flag =
VAR Diff = [Variance]
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(Diff), BLANK(),
        Diff >= 0, "Favorable",
        "Unfavorable"
    )

For expenses, a positive variance is unfavorable, because the actual cost was higher than the budget. Write a separate expense flag so the sign logic is explicit:

Budget Expense = SUM(Budget[Amount])

Expense Variance =
[Operating Expenses] - [Budget Expense]

Expense Variance Flag =
VAR Diff = [Expense Variance]
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(Diff), BLANK(),
        Diff <= 0, "Favorable",
        "Unfavorable"
    )

Keep revenue and expense flags as separate measures. A single flag that tries to handle both usually gets one of them backward.

Balance sheet measures: closing balances are semi-additive

Revenue and expense add up across months. A balance does not. The cash balance on March 31 is the balance on March 31, not the sum of balances for January, February and March. Balance sheet accounts are semi-additive: they add across accounts and not across time.

The standard fix is a closing balance measure that takes the last date in the filter context:

Balance =
CALCULATE(
    SUM(GL[Amount]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

This sums all journal lines up to the last date visible. It is correct for cumulative balances, because a balance sheet account is the running total of everything posted to it. For a period end balance in a month column, use the built-in closing functions instead, which read the last date in the period:

Closing Balance =
CALCULATE(
    SUM(GL[Amount]),
    CLOSINGBALANCEMONTH(SUM(GL[Amount]), 'Date'[Date])
)

If you use CLOSINGBALANCEMONTH or CLOSINGBALANCEYEAR, mark the date table and use the calendar columns consistently. A wrong fiscal year-end argument produces a balance at the wrong date.

Two traps are common in balance sheet models:

Use ISBLANK on the balance sheet to tell "no activity" from "zero balance". A new account with no postings should not show zero on a report that also has zero-balance accounts.

Common ratios

Ratios are measures, not columns. Build each one from measures that are already tested.

Current Assets =
CALCULATE([Balance], Account[StatementGroup] = "Current assets")

Current Liabilities =
-CALCULATE([Balance], Account[StatementGroup] = "Current liabilities")

Current Ratio = DIVIDE([Current Assets], [Current Liabilities])

The liabilities are flipped with a minus sign because they are credit-natural. Without the flip, the current ratio would be negative and most readers would not notice until a board meeting.

Days sales outstanding (DSO) uses the receivables balance and the revenue of the period:

Receivables = CALCULATE([Balance], Account[AccountName] = "Accounts receivable")

Days Sales Outstanding =
DIVIDE([Receivables], [Revenue]) * DAY(EOMONTH(MAX('Date'[Date]), 0))

For a monthly view, DSO uses the number of days in the month. For a rolling 12-month view, replace the day count with 365. State the method in the report notes. Two companies with the same DSO can have different collection performance if they use different methods.

Gross margin percent is the same pattern: DIVIDE([Gross Margin], [Revenue]), formatted as a percent.

Consolidating entities and currencies at a glance

A consolidated report adds several legal entities together. The usual trap is that intercompany transactions are counted twice. The model needs an elimination flag or a separate intercompany account.

Consolidated Revenue =
CALCULATE(
    [Revenue],
    Account[IsIntercompany] = FALSE()
)

Multi-currency models need one more rule: convert each line at a single rate, then sum. Store the rate in a table, FxRate[Currency], FxRate[Month], FxRate[Rate], and convert in a measure:

Revenue (Reporting Currency) =
SUMX(
    GL,
    GL[Amount] * RELATED(FxRate[Rate])
)

This only works if GL is related to a rate table that is already filtered to one rate per currency and month. If it is not, SUMX multiplies each line by a rate from a different month and the total is wrong. Test the conversion against a single entity in one currency first.

For balance sheet accounts, the convention is to translate the closing balance at the period-end rate and income statement items at the average rate. Using one rate for both produces a translation difference that does not reconcile. Name the two rates separately:

Balance (Closing Rate) = [Balance] * [Closing Rate]
Revenue (Average Rate) = [Revenue] * [Average Rate]

Parent totals, subtotals and the footing check

A report foots when every subtotal equals the sum of its children and every total equals the sum of its subtotals. A measure that does not foot is not always a bug in the measure. Sometimes the total row is computed differently, for example as a ratio or a closing balance.

Use ISINSCOPE to find out which row of the matrix the measure is evaluated on. It returns TRUE on a row that is grouped by that column:

Statement Row Type =
IF(ISINSCOPE(Account[Level3]), "Account",
    IF(ISINSCOPE(Account[Level2]), "Subtotal", "Total"))

Build a footing check on the statement. For each level-2 row, it compares the revenue of the level-2 group with the sum of its level-3 children, which are computed one at a time:

Footing Difference =
VAR Parent = [Revenue]
VAR Children = SUMX(VALUES(Account[Level3]), [Revenue])
RETURN ROUND(Parent - Children, 2)

The result should be zero on every row of the statement. A non-zero value means that the parent row uses a different filter than its children, or an account is missing a level. The usual fix is to make the parent total sum the children, instead of recalculating from the ledger.

Pitfalls that show up in finance reports

Symptom Likely cause Check
Ledger check is not zero A journal line is missing, or one side of a journal did not load Count lines per journal in Power Query
Revenue shows as negative Sign flip is missing or applied twice Compare to the trial balance in the ledger
Gross margin percent is above 100% or negative on a small month Revenue is near zero and the cost is not Use DIVIDE with a minimum revenue test, or show the ratio as blank
Total does not match the sum of months A balance measure is summed across time Use a closing balance measure for balance sheet lines
Budget variance is the reverse of expectations Budget sign convention differs from the ledger Check one account by hand
Blank in a row where you expected zero SUM over no rows returns blank Use COALESCE([Measure], 0) only on the display, not in ratios
YTD number changes when you slice by a product A filter on the account table removes the period Filter the date table, not the account table, for period measures

Blanks versus zero

A blank means "no rows matched". Zero means "rows matched and the total is zero". A balance sheet account with no postings in a month is blank. An account that was posted to and netted to zero is zero. Readers treat them differently. Use COALESCE for display, but keep the blank for ratios so DIVIDE can return blank for a division by no data.

Totals that do not foot

Most footing problems come from measures that use ALL or REMOVEFILTERS in one place and a plain filter in another. Check each total against its children after every change. A footing check measure, like the one above, catches the problem at the report layer.

Hierarchies that do not balance

A chart of accounts where an account has two parents, or where a level is empty for some accounts, produces totals that count some amounts twice or not at all. The fix belongs in the source chart of accounts. Add a check that every account has exactly one value in each level before you load the model.

Testing your measures

Before a financial measure goes into a board pack, test it against three figures:

  1. The trial balance for one entity and one month. Totals by account type must match.
  2. The statement from the accounting system for the same period.
  3. A hand calculation in SQL. The SQL for accountants guide shows the trial balance and fund queries you can run against a ledger.

Use DAX Studio or the query view in Power BI Desktop to see the measure result for one filter. Change one filter at a time, and keep the test in a table visual so you can see every row.

Where next

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.