<!-- https://learnsql.cpetoday.com/analyze/power-bi-for-finance -->
# Power BI for finance teams: ledger to dashboard

A finance pack is a one-page answer to three questions: what did we earn, what did we spend, and how does that compare with the budget. This guide builds that pack from start to finish on a real general ledger. You connect to the data, shape it in Power Query, model it as a star, write the measures, lay out the page and publish it.

The sample is `nonprofit`, a regional nonprofit with fund accounting. It has a general ledger, a budget by account and fund, fiscal periods, and cost centers. Every number in this guide came from a query run against that database. The DAX is standard and listed in the places it applies. DAX cannot run in this guide's environment, so each measure says to check it in DAX query view.

If you are new to Power BI, read [Power BI Desktop how-to](/analyze/power-bi-desktop) first. The deeper DAX reference is in [DAX for financial reporting](/analyze/dax-financial-reporting). Time measures are in [Time intelligence](/analyze/time-intelligence). For the server and login details, see [Connect](/connect).

## The build at a glance

Five steps, in order. Check each step before you start the next one.

> Diagram: Five steps in order: Get Data from SQL Server, shape in Power Query, build the star model and DAX measures, lay out the report pages, then publish with a scheduled refresh.

## Step 1: Connect to the ledger

1. In Power BI Desktop, select **Home > Get Data > SQL Server**.
2. Server: `learnsql.cpetoday.com,1433`. Database: `nonprofit`.
3. Choose **Import**. Use Import for a finance pack. DirectQuery makes every visual a live query, which is slow for a page with twelve visuals.
4. In the Navigator, tick these tables under the `dbo` schema: `gl_journal_lines`, `gl_journal_entries`, `gl_accounts`, `funds`, `cost_centers`, `fiscal_periods`, and `budgets`.
5. Select **Transform Data**, not **Load**. You shape the data first.

## Step 2: Shape the data in Power Query

Shaping means four things: join the entry date onto each line, type the columns, make one signed amount, and map each account to a type.

**Join the date.** A line in `gl_journal_lines` has no date. The date is on the entry. Join the two tables on `journal_entry_id`.

```powerquery
let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "nonprofit"),
    Lines = Source{[Schema = "dbo", Item = "gl_journal_lines"]}[Data],
    Entries = Source{[Schema = "dbo", Item = "gl_journal_entries"]}[Data],
    Joined = Table.NestedJoin(Lines, {"journal_entry_id"}, Entries, {"journal_entry_id"}, "Entry", JoinKind.Inner),
    Expanded = Table.ExpandTableColumn(Joined, "Entry", {"entry_date", "fiscal_year", "fiscal_period_id"}, {"entry_date", "fiscal_year", "fiscal_period_id"}),
    Typed = Table.TransformColumnTypes(Expanded, {{"debit", Currency.Type}, {"credit", Currency.Type}, {"entry_date", type date}}),
    Signed = Table.AddColumn(Typed, "Amount", each [debit] - [credit], Currency.Type)
in
    Signed
```

**Keep one sign rule.** The ledger stores debit minus credit. Assets and expenses are positive. Revenue, liabilities and net assets are negative. That is correct for the ledger, but a board reads revenue as a positive number. Keep `Amount` as debit minus credit, then add a display column for revenue.

> Diagram: One revenue line with debit 0 and credit 1,000 becomes a signed amount of minus 1,000, which is multiplied by minus one for display as 1,000 of revenue.

**Map the accounts.** Join `gl_accounts` to bring in the account type. Then use that type to flip the revenue sign. The account type also goes on the Accounts dimension, which Step 3 covers. Rename `account_type` to `Account Type` there, so the measures in Step 4 can find it.

```powerquery
let
    Accounts = Source{[Schema = "dbo", Item = "gl_accounts"]}[Data],
    Merged = Table.NestedJoin(Signed, {"gl_account_id"}, Accounts, {"gl_account_id"}, "Account", JoinKind.LeftOuter),
    Mapped = Table.ExpandTableColumn(Merged, "Account", {"account_number", "account_name", "account_type"}, {"Account Number", "Account Name", "Account Type"}),
    WithDisplay = Table.AddColumn(Mapped, "Display Amount", each if [Account Type] = "Revenue" then [credit] - [debit] else [debit] - [credit], Currency.Type)
in
    WithDisplay
```

Use `Source` from the first query. In Power Query, each step can refer to an earlier step by name, so paste the two blocks into one query, or build the second from the first in the Applied Steps pane.

> **Audit tip.** The total of `Amount` across all lines must be zero for every entry. Run this check in SQL before you trust the model. In the sample it checks 34,377 entries and finds none that fail to net to zero.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT COUNT(*) AS entries_checked,
       SUM(CASE WHEN x.net <> 0 THEN 1 ELSE 0 END) AS not_zero
FROM (SELECT e.journal_entry_id, SUM(l.debit - l.credit) AS net
      FROM dbo.gl_journal_entries AS e
      JOIN dbo.gl_journal_lines AS l ON l.journal_entry_id = e.journal_entry_id
      GROUP BY e.journal_entry_id) AS x;
```

## Step 3: Model it as a star

A star model has one fact table in the middle and dimension tables around it. Facts hold numbers. Dimensions hold the words you filter and group by. Power BI measures run fastest and are easiest to check on this layout. Here the model has one fact for actuals, one for budget, and four dimensions.

> Diagram: A star model. The GL lines fact sits in the middle and joins to four dimensions: Date, Account, Fund and Cost center. A Budget fact sits below and shares the same dimensions.

| Table in the model | Source | Role |
|---|---|---|
| GL Lines | Lines joined to entries (Step 2) | Fact: actual amounts |
| Budget | `budgets` | Fact: budget by year, account, fund and cost center. Rename `budget_amount` to `Budget Amount` |
| Accounts | `gl_accounts` | Dimension: number, name, and `account_type` renamed to `Account Type`. Relate it to GL Lines on `gl_account_id` |
| Funds | `funds` | Dimension: fund, restriction class |
| Cost Centers | `cost_centers` | Dimension: program or support function |
| Date | Built in DAX (below) | Dimension: every day, with fiscal year and period |

**Use a daily date table.** The `fiscal_periods` table has one row per month. Time intelligence functions need one row per day, so build a Date table in DAX. The fiscal year starts on July 1. That matches `fiscal_periods`, where fiscal 2026 runs from 2025-07-01 to 2026-06-30.

```dax
Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 7, 1 ), DATE ( 2027, 6, 30 ) ),
    "Fiscal Year", YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 7, 1, 0 ),
    "Fiscal Period", IF ( MONTH ( [Date] ) >= 7, MONTH ( [Date] ) - 6, MONTH ( [Date] ) + 6 )
)
```

Mark the table as a date table in **Table tools > Mark as date table**, and relate `Date[Date]` to the entry date on the GL Lines table. Check in DAX query view that `Fiscal Period` gives 1 for July and 12 for June.

**Expect blanks on cost center.** In the sample, every expense line has a cost center. Revenue, asset and liability lines have none. A slicer on cost center will hide revenue. That is correct, not an error. Show revenue on the summary page, and use cost center only for the expense detail.

**The budget has no month.** The `budgets` table holds one amount per year, account, fund and cost center. It has no month. You can compare the full year, but you cannot compare a month with the budget unless you spread the budget across months with a rule, such as one twelfth each month. The sample has no spread rule, so the monthly view in this guide shows actuals only.

## Step 4: Write the core measures

The measures below use the names from Step 3. Each measure is standard DAX. Enter them in the model, then check the results in DAX query view against the SQL totals in Step 6.

```dax
Revenue =
CALCULATE ( -SUM ( 'GL Lines'[Amount] ), Accounts[Account Type] = "Revenue" )

Expense =
CALCULATE ( SUM ( 'GL Lines'[Amount] ), Accounts[Account Type] = "Expense" )

Net Surplus = [Revenue] - [Expense]

Budget Revenue =
CALCULATE ( SUM ( Budget[Budget Amount] ), Accounts[Account Type] = "Revenue" )

Budget Expense =
CALCULATE ( SUM ( Budget[Budget Amount] ), Accounts[Account Type] = "Expense" )

Revenue Variance = [Revenue] - [Budget Revenue]

Expense Variance = [Budget Expense] - [Expense]

Expense Variance % = DIVIDE ( [Expense Variance], [Budget Expense] )
```

**Read the variance signs.** Revenue variance is positive when you earned more than budget. Expense variance is positive when you spent less than budget. The expense measure is written as budget minus actual on purpose, so a positive number is good in both rows. Label it that way on the page, so a reader does not have to work it out.

**Year to date and the prior year.** Use the fiscal year end, June 30, in `DATESYTD`. For the same period last year, use `SAMEPERIODLASTYEAR`. The time intelligence guide covers how these behave with a partial year.

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

Revenue Prior Year =
CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
```

**Balance as of a date.** Balance sheet accounts are running totals, not period totals. A cash balance at June 30 is every cash movement up to that date. The pattern below sums every day up to the latest date in the filter.

```dax
Asset Balance =
CALCULATE (
    SUM ( 'GL Lines'[Amount] ),
    Accounts[Account Type] = "Asset",
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
```

Check the cash balance against the SQL result in Step 6 for June 30, 2026.

## Step 5: Lay out the finance pack

A one-page pack answers the three questions at the top and gives the detail below. The layout below is the standard. Each element uses one of the measures above.

> Diagram: A one-page finance pack layout. A row of four KPI cards on top, an income statement matrix with actual, budget and variance on the left, a variance bar chart on the right, and a twelve-month trend along the bottom.

| Element | Measure or field | Note |
|---|---|---|
| KPI cards (top row) | Revenue, Expense, Net Surplus, Expense Variance | Show the current fiscal year to date |
| Income statement matrix (left) | Rows: Account Type, then Account Name. Columns: Actual, Budget, Variance | Use Revenue and Expense measures |
| Variance bars (right) | Revenue Variance, Expense Variance, Net | Positive means favorable |
| Trend (bottom) | Revenue and Expense by month | Show actuals only, since budget has no month |

The FY2026 totals in this layout tie to the sample. Revenue $7.37M, expense $7.31M, net surplus about $0.06M, expense $19K under budget. Those are the numbers Step 6 checks.

**Look at the trend before you publish.** December 2025 revenue is $1.34M, more than twice the typical month. It is probably a large grant or a year-end pledge. In a real pack, a month like that needs a note on the page, or the first question at the board meeting will be what happened in December.

## Step 6: Tie the pack to the ledger

A report that does not tie to the ledger is wrong, however good it looks. Run the SQL below, then compare each figure with the matching measure in DAX query view. The figures should match to the cent.

**Runs on:** `nonprofit`, SQL Server. This is the budget and actual tie for fiscal 2026.

```sql
SELECT a.account_type, 'Budget' AS measure, SUM(b.budget_amount) AS amount
FROM dbo.budgets AS b
JOIN dbo.gl_accounts AS a ON a.gl_account_id = b.gl_account_id
WHERE b.fiscal_year = 2026
GROUP BY a.account_type
UNION ALL
SELECT a.account_type, 'Actual (debit minus credit)', SUM(l.debit - l.credit)
FROM dbo.gl_journal_lines AS l
JOIN dbo.gl_journal_entries AS e ON e.journal_entry_id = l.journal_entry_id
JOIN dbo.gl_accounts AS a ON a.gl_account_id = l.gl_account_id
WHERE e.fiscal_year = 2026
GROUP BY a.account_type
ORDER BY 1, 2;
```

**What it returns.** Expense actual 7,314,951.09 against budget 7,334,200.00. Revenue actual 7,374,811.79 (shown as negative, because it is a credit) against budget 7,342,100.00. Your `Expense` and `Budget Expense` measures should show those values. Revenue shows the sign flipped, which is why the Revenue measure uses a minus sign.

The monthly trend should add up to the same annual totals. This query returns revenue and expense by month for fiscal 2026. Its twelve rows sum to the totals above.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT p.period_number, p.start_date,
       SUM(CASE WHEN a.account_type = 'Revenue' THEN l.credit - l.debit ELSE 0 END) AS revenue,
       SUM(CASE WHEN a.account_type = 'Expense' THEN l.debit - l.credit ELSE 0 END) AS expense
FROM dbo.gl_journal_lines AS l
JOIN dbo.gl_journal_entries AS e ON e.journal_entry_id = l.journal_entry_id
JOIN dbo.gl_accounts AS a ON a.gl_account_id = l.gl_account_id
JOIN dbo.fiscal_periods AS p ON p.fiscal_period_id = e.fiscal_period_id
WHERE e.fiscal_year = 2026
GROUP BY p.period_number, p.start_date
ORDER BY p.period_number;
```

Balance sheet figures tie the same way. This query gives the asset balances at June 30, 2026, the end of fiscal 2026. Compare it with your `Asset Balance` measure set to that date.

**Runs on:** `nonprofit`, SQL Server.

```sql
SELECT a.account_number, a.account_name, SUM(l.debit - l.credit) AS balance
FROM dbo.gl_journal_lines AS l
JOIN dbo.gl_journal_entries AS e ON e.journal_entry_id = l.journal_entry_id
JOIN dbo.gl_accounts AS a ON a.gl_account_id = l.gl_account_id
WHERE a.account_type = 'Asset' AND e.entry_date <= '2026-06-30'
GROUP BY a.account_number, a.account_name
ORDER BY a.account_number;
```

**What it returns.** Seven asset accounts. Cash operating is $272,404.68, savings and money market is $900,000.00 and investments are $1,308,741.92. Pledges receivable, grants receivable and property are the rest.

## Step 7: Publish and refresh

Publish from Power BI Desktop with **Home > Publish**, and pick a workspace. Once it is in the service, set a scheduled refresh under the dataset settings.

Three checks before you schedule a refresh:

- **Connection.** Scheduled refresh runs in the cloud. The server has to be reachable from the Power BI service, and the credentials have to be entered in the service. Check [Connect](/connect) for the server details before you schedule anything.
- **Login.** Use a read-only login. A finance pack never needs write access to the ledger.
- **Refresh time.** Schedule the refresh after the close. A refresh at 6 AM on the first business day shows whatever has been posted so far, so state the as-of date on the page.

## What the sample cannot show

- **Budget by month.** The sample has one budget amount per year. A monthly budget needs a spread rule.
- **Cost center on revenue.** Only expenses carry a cost center. A revenue-by-program view needs a different dimension, such as the fund's program.
- **Approvals and drafts.** The ledger has posted entries only. A pack that includes draft entries needs a status field.
- **Currency.** All amounts are in US dollars.

Once you have the pack, the next step is the detailed variance work in [DAX for financial reporting](/analyze/dax-financial-reporting), and the period logic in [Time intelligence](/analyze/time-intelligence).
