Power BI for finance teams — from raw ERP to board-ready MIS

Every finance team I've worked with has the same month-end ritual: export from the ERP, paste into the master Excel, fix the broken VLOOKUP, format the P&L tab, email a PDF to the MD — only to discover the numbers changed because someone posted a late entry. The report is stale before it lands in the inbox. After running this cycle for a ₹700 Cr business, I replaced it with a self-refreshing Power BI MIS that promoters and management open on their phones before the morning stand-up. Here's exactly how.

This isn't a tutorial on clicking around Power BI Desktop. It's about the decisions that determine whether your dashboard becomes the single source of truth or another ignored tab nobody opens after the first month.

Get the data model right first

Most finance teams approach Power BI the way they approach Excel — drag in a flat export, throw some charts on it, call it a dashboard. This works for one report, for one month, until someone asks why the numbers don't match the GL. The foundation that fixes all of this is a proper star schema.

The concept is simple: you have fact tables that hold transactions, and dimension tables that hold the context. For a finance MIS, your core tables look like this:

• Fact: GL / transactions — every posted journal line with amount, date key, account key, cost centre key, and entity.
• Fact: Sales — every shipped sales line with revenue, quantity, date key, customer key, product key.
• Dim: Date — one date table covering your full reporting range, with year, quarter, month, week, and a flag for working days. One date table. Not one per fact.
• Dim: Account — your chart of accounts with account groups and P&L line mapping.
• Dim: Cost Centre — cost centres or departments, mapped to business units.
• Dim: Customer — customer master with segment, geography, and domestic vs export flag.
• Dim: Product — product master with category, brand, and margin class.

The relationships flow from fact to dimension — many-to-one, never the reverse. This is 80% of the work. If the model is right, every measure you write later just works. If it's wrong, you spend the next six months debugging filter context errors and wondering why YTD is double-counting.

The single most common mistake I see: flat spreadsheet imports. Someone exports a report from the ERP with account name, cost centre name, month, and amount all in one row. It looks convenient. It destroys your ability to slice correctly the moment you add a second fact table, because now every dimension is duplicated inside the fact with no clean relationship.

Connect to the source, don't export

The manual export is the bottleneck. You export on the 7th, someone posts a correction on the 8th, and the dashboard is wrong. The fix is to connect Power BI directly to the ERP so that refresh pulls live data every time.

On Dynamics 365 Business Central, this means connecting via OData feeds — Business Central exposes pages and queries as OData endpoints. Power Query connects directly, and you write M transformations against the live source rather than a CSV. On SQL-based ERPs, you connect via the DirectQuery or import connector to the underlying database. Either way, the principle is the same: Power Query is your ETL layer, not your inbox.

Once the model is published to the Power BI Service, set up a scheduled refresh — daily at 6 AM works for most finance teams, or twice daily if you want intraday visibility. For on-premise databases you'll need an on-premises data gateway installed on a machine that can reach the database server. It's a 20-minute setup and it runs silently in the background.

The result: every morning, the dashboard reflects last night's final posting position. No manual steps, no emails, no version confusion.

The DAX that finance actually needs

DAX has a reputation for being complex. For a finance MIS, you need maybe eight measures, and all of them are straightforward once you see the pattern. Write these as measures, not calculated columns — measures compute at query time in filter context, which is what makes slicers and drill-downs work correctly.

The core set:

• Actual: SUM(Fact_GL[Amount]) — aggregate posted transaction amounts.
• Budget: SUM(Fact_Budget[Amount]) — aggregate from your budget fact table.
• Variance: [Actual] - [Budget] — simple subtraction; Power BI handles sign convention via your account dimension.
• Variance %: DIVIDE([Variance], ABS([Budget]), 0) — use DIVIDE, never divide by hand, to handle zero-budget lines cleanly.
• YTD Actual: TOTALYTD([Actual], Dim_Date[Date]) — time intelligence wraps your base measure; the date table does the work.
• Prior Year Actual: CALCULATE([Actual], SAMEPERIODLASTYEAR(Dim_Date[Date])) — one line gives you last year in the same period.
• YoY Variance %: DIVIDE([Actual] - [Prior Year Actual], ABS([Prior Year Actual]), 0)
• Shipped Sales (Domestic): CALCULATE([Sales Amount], Dim_Customer[Type] = "Domestic") — filter context applied via CALCULATE.

That's it. The entire MIS that the board reads is built on variations of these eight measures. Everything else — waterfall charts, KPI cards, conditional formatting — is a visual layer on top.

Dashboards that get read

A dashboard that requires a five-minute orientation is a dashboard that gets ignored. Design for a 30-second read by the MD: the most important number should be visible without scrolling, variance should be colour-coded red/green so the eye goes there first, and every visual should answer exactly one question.

The pages I built for the ₹700 Cr MIS, in order of how often they were opened:

• Budget vs Actual — P&L summary: Revenue, gross margin, EBITDA, and PAT against budget for the month and YTD. KPI cards at the top, variance table below. This is the first page everyone opens.
• Sales — Shipped / Domestic vs Export: Total shipped sales split by domestic and export, with product category and top-10 customer concentration. A line chart of monthly shipped vs last year. Promoters watch this daily.
• P&L Waterfall: Actual revenue → gross profit → EBITDA bridge, showing each cost line as a waterfall step. Instantly visible where margin is going.
• Marketing & Cost-Centre Variance: Each marketing leader's spend vs budget, colour-coded. Same view for all cost centres. This page replaced a 40-row Excel table that nobody read.
• Product Concentration: Revenue by product group, Pareto chart of top-20 SKUs, margin by category. Useful for product reviews.

Navigation matters. Use a persistent left-panel navigation with labelled buttons — not the default Power BI tab bar — so the report feels like a product, not a file.

Governance and trust

The hardest part of rolling out a Power BI MIS isn't the technical build. It's the moment someone looks at the dashboard, looks at their Excel, and says "these numbers don't match." If you can't resolve that in 60 seconds, the dashboard dies.

Build trust into the report from day one:

• Reconciliation tile: A KPI card on the summary page that shows "Dashboard GL balance vs ERP GL balance" — ideally ₹0 variance. When people see the dashboard ties back to the source, they stop checking it against their own spreadsheets.
• Refresh timestamp: A card showing "Last refreshed: [date and time]." Simple, but it tells users whether what they're reading is live or stale.
• Row-level security (RLS): If cost-centre managers should only see their own variance, configure RLS roles in Power BI Desktop and assign users in the Service. It's a 30-minute setup that prevents the "why can marketing see my budget" conversation.
• One workspace, one dataset: All reports in the organisation should connect to a single certified dataset published by finance. No parallel datasets built by business units from their own exports.

Real-time finance isn't about speed for its own sake. It's about the difference between a management team making decisions on what happened last month, and a management team making decisions on what is happening now.

The CMA data package that banks request — projected financials, actual vs projected variance, receivables and payables ageing — can be pulled directly from the same dataset. When the data model is clean and the measures are right, CMA output is a report template away, not a separate exercise.

Where to start if your team is small

Don't try to build all five pages at once. Start with one report, done properly: budget vs actual for the current month. Get the GL fact table connected, build the date dimension, write the Actual and Budget measures, and put up a clean P&L variance table. Publish it, schedule the refresh, and show it to the MD on T+1 of the next month-close.

That single report, landing reliably on T+1 with accurate numbers, will do more to establish the finance function's credibility than six months of Excel MIS work. Once people see it works, the appetite for the sales split, the waterfall, and the cost-centre variance follows naturally.

The same T+0 / T+5 close discipline that feeds a clean month-end also feeds this dashboard. The close cadence and the MIS are the same system — one produces clean data, the other surfaces it. Invest in the close process first; the dashboard is the output, not the work.

Power BI itself is not the hard part. Microsoft has made the tool genuinely accessible — a finance professional with Excel fluency can learn enough Power Query and DAX in a weekend to build something useful. The hard part is the data model and the discipline: getting the star schema right, connecting to the live source instead of exporting, writing measures that behave correctly under every filter combination, and maintaining the close process that keeps the underlying data clean.

Get those right, and the board-ready MIS takes care of itself.