<!-- https://learnsql.cpetoday.com/analyze/dax-financial-reporting -->
# DAX for financial reporting

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](/analyze/fundamentals). It also uses [CALCULATE](/functions/dax/calculate), [SUMX](/functions/dax/sumx) and [DIVIDE](/functions/dax/divide), which are listed in the [function reference](/analyze/functions).

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](/analyze/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:

- `GL[Amount]` = debit minus credit, for every line, in the ledger's own currency.
- Assets and expenses have a normal debit balance, so their total is positive when they are on the books.
- Liabilities, equity and revenue have a normal credit balance, so their total is negative.

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:

```dax
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:

```dax
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.

```dax
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.

```dax
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:

```dax
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:

```dax
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.

```dax
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

```dax
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

```dax
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](/analyze/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.

```dax
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:

```dax
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:

```dax
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:

```dax
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:

- **Summing balances across months.** A matrix with months across the top shows the cumulative balance in each cell if you use `SUM` on the amount. It is correct only if the measure is the closing balance.
- **Totals that add monthly closing balances.** The grand total of a closing balance measure must show the last closing balance, not the sum of the months. Check the total column against a manual lookup.

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.

```dax
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:

```dax
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.

```dax
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:

```dax
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:

```dax
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:

```dax
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:

```dax
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](/analyze/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

- [DAX fundamentals](/analyze/fundamentals): measures, context and the sign rules that this guide builds on.
- [DAX deep end](/analyze/dax-deep-end): how CALCULATE and the storage engine decide what a measure returns.
- [DAX pattern cookbook](/analyze/patterns): variance, prior year and balance patterns you can adapt.
- [Power Query for accounting data](/analyze/power-query-accounting-data-prep): clean the ledger export before it reaches the model.
- [SQL for accountants](/analyze/sql-for-accountants): query the trial balance and reconcile to the ledger.
- [Function reference](/analyze/functions): look up any function used here.
