Financial dashboard in Power BI

A financial dashboard brings the key figures for finance and management together in one place: revenue, margin, costs, cash flow, receivables, and budget versus actuals. In Power BI, these figures are defined as DAX measures in a single semantic model, so every report shows the same number.

It is part of our Power BI dashboards, which we build around the decisions they support. For a CFO dashboard, those decisions often concern the month-end close and the forecast for the rest of the year.

Usually the general ledger, accounts receivable and accounts payable from the accounting system, orders and inventory from the ERP system, sometimes the pipeline from the CRM system, and the budget from Excel or a planning tool. We have separate pages for commonly used systems: Exact Online, AFAS SB, Twinfield and AFAS Payroll.

If a source is on a server in your own network, Power BI retrieves the data through the on-premises data gateway, which only makes outbound connections.

Start by agreeing on a reporting structure: assign every general ledger account in each set of accounts to a fixed line in the profit and loss statement or balance sheet using a mapping table. If entities use different charts of accounts, the table accounts for those differences.

Transactions between your own entities, known as intercompany transactions, must be eliminated during consolidation. Record which accounts these involve and show the consolidated figure alongside the figure for each entity, so the controller can reconcile them.

The budget is usually at a higher level than the transactions, for example by month and cost centre. Microsoft calls this a fact table with a higher granularity. You link the budget to the date table using the first day of each period.

If someone drills down to a single day, the budget will show a blank or misleading figure without additional logic. A measure that checks the level at which the data is filtered prevents this.

Yes. For time calculations such as year-to-date totals, Power BI needs a date table: one row per date, with no gaps, covering complete years. According to Microsoft, those years do not have to be calendar years, so a financial year starting in July works too.

If you report by week or follow a 4-4-5 structure, the newer calendar-based time intelligence, currently still in preview, offers more flexibility.

With row-level security (RLS). You define roles in the model using a DAX filter, for example by entity or cost centre, and assign users or security groups to them. RLS applies to people with the Viewer role in the workspace, not to Admins, Members or Contributors.

For more on how we set up workspaces, roles and apps, see our Power BI consultant page.

For most finance teams, a scheduled overnight refresh is enough, with an extra refresh after the month-end close. You can refresh large transaction tables incrementally: Power BI divides the table into partitions and refreshes only the recent ones, using the RangeStart and RangeEnd parameters.

Make the refresh window wide enough. If a closed period is reopened and corrected later, that period needs to be loaded again too.

When you have one set of accounts, the standard reports in your accounting software are sufficient, and nobody needs to see figures from other systems alongside them. Or when you have not yet agreed on what counts as revenue: in that case, you would simply recreate the confusion in Power BI.

If that is the case, start with data governance or the source itself. If your question goes beyond finance, read about business intelligence.