Power BI for Finance: A Monthly P&L Dashboard

A monthly P&L dashboard is one of the highest-value reports you can build in Power BI, and one of the easiest to get subtly wrong. The math is simple, but signs flip, budgets live at a different grain than actuals, and time intelligence breaks quietly without a proper date table. This guide walks through a clean, repeatable build: the data you need, the model, the DAX, and a layout finance leaders will actually read.

Start with the data you actually need

A monthly P&L in Power BI comes down to four inputs. You don't need all four to start, but the report gets far more useful as you add them.

• Actuals (the ledger). A transaction or general-ledger export with at least a date, an account, a department or cost center, and an amount. This is your source of truth.
• Chart of accounts. A lookup that maps each account number to a P&L category (Revenue, COGS, Opex) and a sub-category (Salaries, Software, Marketing, and so on). This is what lets you roll the ledger up into a real statement instead of a flat list.
• Budget. Planned amounts, ideally at the same grain you want to compare against — month x department x category. If your budget is annual, you can spread it evenly across months in Power Query.
• Date table. A dedicated calendar table covering every day in your reporting range. More on why this matters below.

If your data is in CSV or Excel, that's fine — Power Query (the Transform Data window in Power BI Desktop) is built exactly for this. Power BI Desktop is the free Windows app where all modeling and report building happens; you only need a paid license when you publish to the Power BI Service to share with others.

Model it as a star schema

Resist the urge to build everything in one giant flat table. A P&L models cleanly as a star schema: fact tables in the middle, dimension (lookup) tables around the edges.

Table	Role	Key columns
Actuals	Fact	Date, AccountID, DepartmentID, Amount
Budget	Fact	Month, DepartmentID, CategoryID, BudgetAmount
Accounts	Dimension	AccountID, Category, SubCategory
Departments	Dimension	DepartmentID, Department
Date	Dimension	Date, Year, MonthNo, MonthName

Relationships flow one-to-many from the dimensions into the facts. In Power BI's Model view, drag Date[Date] to Actuals[Date], Departments[DepartmentID] to both fact tables, and so on. The collection of tables, relationships, and measures is your semantic model (Microsoft renamed this from "dataset" in late 2023 — you'll still see both terms in the wild).

The payoff: one slicer on Departments[Department] filters revenue, COGS, Opex, and budget consistently, with no extra work. For a deeper walkthrough, see our guide on the star schema versus a flat table.

Fix the sign convention before anything else

This is the step most people skip and then spend an afternoon debugging. General ledgers typically store credits and debits with opposite signs — revenue might come through negative and expenses positive, or the reverse. If you sum raw amounts, net income will be wrong or inverted.

Decide on one convention — the cleanest is positive revenue, positive expenses, and Net = Revenue − COGS − Opex — and normalize on the way in. A calculated column on the ledger, driven by the account category, works well:

Signed Amount =
VAR Cat = RELATED ( Accounts[Category] )
RETURN
    IF (
        Cat = "Revenue",
        Actuals[Amount] * -1,   -- flip if revenue arrives as a credit
        Actuals[Amount]
    )

Adjust the multiplier to match your source. The principle: get one consistently signed Signed Amount first, then every downstream measure stays simple.

Build a proper Date table

Time intelligence functions like SAMEPERIODLASTYEAR and PREVIOUSMONTH need a continuous date dimension — one row per day, no gaps, covering your full range. Relying on the date column inside your ledger will eventually break, because ledgers have missing days. Create the table once with DAX:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNo", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmm" ),
    "Year-Month", FORMAT ( [Date], "yyyy-mm" )
)

Then go to Table tools → Mark as date table and choose Date[Date]. Sort MonthName by MonthNo (Column tools → Sort by column) so months read Jan–Dec instead of alphabetically. This one-time setup is what makes every MoM and YoY measure below just work.

Write the core P&L measures

Build a small set of base measures, then layer comparisons on top. Keep the count lean — a handful of well-named measures beats dozens of one-offs.

Revenue =
CALCULATE ( SUM ( Actuals[Signed Amount] ), Accounts[Category] = "Revenue" )

COGS =
CALCULATE ( SUM ( Actuals[Signed Amount] ), Accounts[Category] = "COGS" )

Opex =
CALCULATE ( SUM ( Actuals[Signed Amount] ), Accounts[Category] = "Opex" )

Gross Profit = [Revenue] - [COGS]

Net Income = [Revenue] - [COGS] - [Opex]

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

Always use DIVIDE rather than / for margins — it returns blank instead of an error when revenue is zero.

Now the time intelligence. Each comparison is a small wrapper around a base measure:

Net Income PM =
CALCULATE ( [Net Income], PREVIOUSMONTH ( 'Date'[Date] ) )

Net Income MoM % =
DIVIDE ( [Net Income] - [Net Income PM], [Net Income PM] )

Net Income PY =
CALCULATE ( [Net Income], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Net Income YoY % =
DIVIDE ( [Net Income] - [Net Income PY], [Net Income PY] )

For budget variance, since the budget lives in its own fact table, sum it directly and subtract. Note that a measure can't share the exact name of a table, so name it Budget Amount rather than Budget:

Budget Amount = SUM ( Budget[BudgetAmount] )

Variance to Budget = [Net Income] - [Budget Amount]

Variance to Budget % =
DIVIDE ( [Net Income] - [Budget Amount], [Budget Amount] )

One note on percentages: rather than writing a separate MoM, YoY, and variance measure for every line item, build the deltas only for the totals you care about, then use Power BI's "Show value as" option and legend/column splits for the rest. It keeps the model readable.

Lay it out like a statement, not a wall of charts

Finance readers scan a P&L top to bottom. Mirror that.

• Top row — KPI cards. Revenue, Gross Profit, Net Income, and Variance to Budget, each showing its MoM or YoY % as the trend. These answer "how did we do" in two seconds.
• Left/center — the statement. A matrix visual with P&L categories down the rows (Revenue → COGS → Gross Profit → Opex → Net Income) and columns for Actual, Budget, Variance, and Variance %. Use conditional formatting so negative variances turn red. This is the heart of the report and the view your CFO will live in.
• Right — trend. A line or combo chart of Net Income by month, with prior year as a faint second line.
• Bottom — by department. A bar chart of Net Income or Opex by department, which turns the single P&L into something each department head can own.
• Slicers. Year, month, and department across the top. Thanks to the star schema, every visual responds to all three at once.

A couple of finishing touches separate a usable report from a polished one: format currency measures consistently (Measure tools → Format), and apply a clean theme so colors and fonts match your brand. You can generate a JSON theme file in a few minutes with the free Power BI theme generator, then import it under View → Themes.

Save it the right way

You can save as a single .pbix file, but the newer .pbip (Power BI Project) format stores the report and semantic model as plain-text folders — far better for version control, diffing, and reuse month to month. Either way, when next month's numbers land you simply refresh the data and every measure recalculates.

If you'd rather skip the modeling and DAX, that's exactly what Instant PowerBI does. Send your ledger, chart of accounts, and budget files (CSV or Excel), and you get back a finished, branded Power BI report — star schema, signed amounts, date table, and the variance and YoY measures already built. It opens in free Power BI Desktop, so it's yours to refresh and extend every month. Start with your data and get a dashboard back.