Downloads

Power BI for finance teams: ledger to dashboard

A start-to-finish build: connect to the ledger, shape it, model it, write the measures and lay out a monthly finance pack.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Build a finance model from start to finish: connect, shape, model, measure and publish.
  • Lay out a monthly finance pack in Power BI.
  • Tie the report totals back to the ledger.
  • Explain what the sample data cannot show, and plan around it.

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 first. The deeper DAX reference is in DAX for financial reporting. Time measures are in Time intelligence. For the server and login details, see Connect.

The build at a glance

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

Get Data SQL Server, Import Power Query Types, signs, mapping Star model Measures in DAX Report One page per pack Publish Scheduled refresh
The build order from start to finish. Each step has one job, and each step is checked before the next one starts.

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.

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.

GL line, revenue Debit 0 Credit 1,000 Signed amount 0 minus 1,000 = -1,000 Shown as revenue Multiply by -1 = 1,000
Sign convention. The ledger stores debit minus credit. Revenue is a credit, so it comes out negative. Flip the sign once, in Power Query or in the model, and label it clearly.

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.

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.

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.

GL lines (fact) Debit, credit, signed amount Entry date, account, fund Date Fiscal year, quarter, period Account Type, statement line Fund Restriction class Cost center Expense lines only Budget (fact) Amount by year and account Budget joins to Account, Fund and the fiscal year on the Date table.
The model for the ledger report. The GL lines fact holds the amounts. Date, Account, Fund and Cost center are the dimensions you filter and group by. Budget uses the same Account, Fund and fiscal year.
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.

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.

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.

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.

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.

Monthly finance pack, FY2026 Revenue 7.37M Expenses 7.31M Net 0.06M Expenses vs budget 19K under Line Actual Budget Variance Revenue 7.37M 7.34M +33K Expenses 7.31M 7.33M 19K Net 0.06M 0.01M +52K Variance to budget +33K +19K +52K Revenue Expense Net Twelve-month trend (shape only)
Layout of a one-page monthly finance pack, drawn with FY2026 totals from the nonprofit sample (US dollars, rounded). The trend line shows shape only.
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.

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.

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.

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:

What the sample cannot show

Once you have the pack, the next step is the detailed variance work in DAX for financial reporting, and the period logic in Time intelligence.

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.