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.
Step 1: Connect to the ledger
- In Power BI Desktop, select Home > Get Data > SQL Server.
- Server:
learnsql.cpetoday.com,1433. Database:nonprofit. - Choose Import. Use Import for a finance pack. DirectQuery makes every visual a live query, which is slow for a page with twelve visuals.
- In the Navigator, tick these tables under the
dboschema:gl_journal_lines,gl_journal_entries,gl_accounts,funds,cost_centers,fiscal_periods, andbudgets. - 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.
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.
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.
| 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.
| 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:
- 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 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, 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.