Power BI Dashboard Recipes Wired to Dynamics 365

Recipes, not the default apps

Microsoft publishes a Power BI app for every Dynamics 365 functional area — Finance, Sales, Purchasing, Inventory, Projects. Those apps are excellent starting points and the right thing to install first; they are not the end of the road. A default app answers 'what happened across this whole module?', while the dashboard that actually moves a number answers a single, narrow question like 'which customers will push us past our overdraft this month?' or 'which SKUs are we over-stocking relative to demand?'. This guide is a recipe book for those narrow, high-leverage dashboards.

Each recipe is deliberately cross-app. That is the distinction from a Business Central-only treatment: the same cash-flow logic applies whether your ledger lives in Business Central, in Finance & Operations, or both, and the same aging logic applies whether receivables sit in F&O or BC. We name the specific source for each app family — Entity store and Export to Data Lake (or Link to Microsoft Fabric) for F&O, API V2.0 pages for BC, the Dataverse TDS endpoint (or Fabric Link) for D365 Sales and Customer Service — so you can build the same dashboard against whichever platform you actually run.

Treat a recipe as four parts: the source data, a small star-schema model, a handful of DAX measures, and a set of visuals plus a refresh plan. Skip any one and the dashboard either lies (wrong model), is slow (no refresh strategy), or is unread (wrong visual choices). The recipes below follow that same four-part shape so you can apply them consistently.

The shared foundation every recipe needs

Before any single recipe, set one foundation that all of them share. Build a proper Date table that marks your fiscal calendar (fiscal year, fiscal quarter, fiscal month, and an ISO-style date key), and mark it as a date table in the model. Almost every recipe here needs time intelligence — rolling 12 months, week-to-date, aging buckets — and that only works when there is a single, marked Date dimension related to every fact table on a date key.

Use a star schema, not the flat transactional tables the connectors hand you. Dynamics exposes wide tables (a sales invoice line with 60+ columns, an item ledger entry with dozens); reporting directly off them is the single most common cause of slow dashboards and broken totals. Carve them into one fact table per process (sales, purchases, ledger entries, inventory movements) surrounded by slim dimensions: Customer, Vendor, Item, Ledger Account, Company/Legal Entity, and the Date table. This keeps DAX measures short and refreshes fast. Microsoft Fabric documentation frames the same idea: a Power BI semantic model is typically a star schema with facts that represent a domain and dimensions that let you slice it.

Decide storage mode deliberately. Import mode caches compressed data in memory and gives sub-second queries, which is right for the vast majority of ERP reporting; schedule a refresh rather than chasing real-time. DirectQuery leaves data in the source and queries it live — appropriate only when freshness truly matters or volumes exceed what you can import. A composite model, where large fact tables stay in Import and a small recent window uses DirectQuery via a hybrid table, is the professional middle path for near-real-time operational dashboards, but hybrid tables require a Premium or Fabric capacity.

When mid-market growth outgrows connector-only Import models, the Fabric path is the durable upgrade — not a rewrite of your recipes. Practitioners document the same pipeline repeatedly: data ingestion → lakehouse (bronze/silver) → dataflows or notebooks → warehouse or curated lakehouse views → Power BI semantic model → reports. In Fabric you create semantic models on lakehouse, warehouse, or mirrored items; Direct Lake mode can consume parquet files from OneLake without a full Import copy, combining Import-like speed with fresher lake data, and it falls back to DirectQuery when SKU limits or unsupported features are hit. Default semantic models stopped auto-creating for new warehouse/lakehouse/mirrored items (from September 2025) and older defaults were decoupled by late 2025 — plan on intentional semantic models you own, not auto-generated ones.

Finally, set the workspace and licensing baseline. Building and publishing happens in a workspace backed by the right capacity — Power BI Pro per user is enough for small teams; a Fabric F-SKU (or Premium P-SKU, for existing customers) is the threshold where free users can consume shared content, hybrid tables and Direct Lake become available, and scheduled refresh rises from about eight times per day (Pro) to about forty-eight (PPU/capacity). Every recipe below assumes this foundation is already in place.

Which Dynamics 365 source feeds each recipe

Dynamics 365 is not one database. The three app families expose analytical data differently, and picking the right source for a recipe is a performance decision, not a style choice. The table below is the routing table for every recipe in this guide: match your platform to the source, then follow that source's connection pattern.

When a company runs more than one family — for example, Business Central in subsidiaries with Finance & Operations at the corporate level, or Dataverse-based D365 Sales alongside an F&O ledger — the recipes still apply; you simply bring each fact table into the same semantic model under the shared Date and Company dimensions. That is the value of building to a recipe rather than to a single app's default report: the same cash-flow or revenue logic reconciles across sources, and the cross-source reconciliation (pipeline in Dataverse booked to revenue in F&O) is usually the insight worth building.

Link to Microsoft Fabric (from Power Apps) is the 2025–2026 default path when you want ERP facts in OneLake without standing up your own Synapse export stack: Dataverse and Dynamics 365 Finance and Operations tables can land as shortcuts into a Fabric lakehouse with a SQL endpoint and a Power BI semantic model, with no separate ETL storage account required. Microsoft continues to invest in low-latency sync for finance and operations tables. Keep Export to Data Lake / Azure Synapse Link where you already operate it; treat Fabric Link as the greenfield or simplification path, not a forced migration overnight.

Cash flow and liquidity dashboard

The cash flow dashboard answers 'do we have the liquidity to meet obligations over the next 13 weeks?' Wire it to the general ledger cash and bank accounts and to open receivables and payables. In F&O that means Bank accounts and Ledger entities from the Entity store, Data Lake, or Fabric Link; in BC it is the Bank Account Ledger Entries and Vendor/Customer Ledger Entries via API V2.0; in either, the bank account balance is the opening position and scheduled AR receipts minus AP payments form the forward curve.

The model is a single cash fact at the grain of one row per expected cash movement, with dimensions for Date, Bank Account, Company, and Counterparty (customer or vendor), plus a Movement Type dimension (receipt, disbursement, forecast receipt, forecast disbursement). Mark the Date table. The opening balance is a base measure summed from posted ledger entries; the forecast portion is summed from open invoices and open purchase orders using their due dates as the cash date. Grain warning: never mix posted GL lines and open document lines in one fact without a Movement Type — otherwise double-counting or silent drops wreck the waterfall.

Build three visuals: a line chart of projected closing cash by week with a horizontal reference line at the minimum operating balance, a waterfall from opening balance through receipts and disbursements to the closing position, and a small table of the top ten counterparty exposures that could break the minimum. Set the refresh to run before the weekly cash meeting — daily is plenty, because the forward curve only shifts when invoices or payables change.

AR aging and collections dashboard

The AR aging dashboard answers 'who owes us, how late, and where should collections focus this week?' Source it from open customer invoices: in F&O the Customer invoice and Settlement entities, in BC the Customer Ledger Entries with their due dates and remaining amounts. If you run D365 Sales on Dataverse, you can enrich the same dashboard with opportunity and account ownership so a collector sees the sales rep accountable for each account — a small Dataverse join that materially improves collection outcomes.

SQLBI's pattern for AR aging is worth matching: OLTP invoice/payment tables are almost never report-ready; normalize movements (invoice, payment due, payment) into a single fact, then materialize an AR Detail grain at the intersection of amounts due and payments allocated to due dates. That lets one outstanding-amount measure sliced by aging band work without five near-identical measures. Import as-is and you fight partial payments, multi-term invoices, and trend-over-time aging with brittle DAX.

Build a disconnected Aging Bands table with rows for Current, 1–30, 31–60, 61–90, and Over 90 days, each carrying a lower and upper day boundary. Disconnected means no relationship to the fact; the measure uses it as a slicer target — or assign a band label with a SWITCH on days past due. Days past due is DATEDIFF between the due date and the as-of date (TODAY() for live aging, or a selected Date for historical snapshots). Grain warning: partial payments and multi-installment terms need allocation to due dates; a single remaining-amount column without due-date split will mis-bucket split invoices.

Visuals: a matrix of customer by aging band with conditional formatting that turns the Over-90 cell red, a bar chart of AR outstanding by collector or sales rep to focus this week's calls, and a DSO trend line against a target. Refresh this one daily — receivables move every day, and stale aging is the most common reason a collections dashboard gets ignored.

Inventory turns and availability dashboard

The inventory dashboard answers 'where is our working capital trapped, and what should we stop buying?' Source it from item ledger entries (F&O) or Item Ledger Entries and Value Entries (BC), splitting cost of goods sold (consumption issues) from inventory on hand valued at cost. The model is an Inventory fact at the grain of item plus location plus date, with Item, Location, Company, and Date dimensions; value the fact at cost so turnover math works.

Availability is the operational counterweight to turnover. Bring in gross requirement (forecast and sales orders) and scheduled receipts (purchase and production orders) so the dashboard shows, per item, whether incoming supply covers demand. A shortage measure flags items where scheduled receipts plus on-hand are less than gross requirement within the planning horizon. This is the pair purchasing actually needs: turnover tells you what to stop buying, availability tells you what to expedite.

Grain warning: mixing inventory value at standard cost with COGS at actual cost silently breaks turns. Pick one cost basis and document it. Also keep quantity and value on separate measure paths if multi-UOM items exist — never SUM mixed units. Visuals: a scatter plot of days of supply (x) against item value (y) so the expensive, slow-moving quadrant jumps out; a table of top dead-stock items by value with a last-issue-date column; and a shortage list sorted by demand date. Refresh daily during business hours.

Sales pipeline and revenue dashboard

The sales dashboard answers 'is the pipeline healthy enough to hit the number, and where is revenue concentrated?' This recipe lives on Dataverse, because D365 Sales (Opportunity, Quote, Invoice) and Customer Service data sit there. Connect with the Dataverse connector, enable the TDS endpoint in the Power Platform admin center, and remember the per-query ceiling: each TDS query is capped at roughly 80 MB, so select only the columns you need and apply date and status filters in Power Query before anything reaches the model — or land tables via Link to Microsoft Fabric and build Import/Direct Lake models for history.

Model one Opportunity fact at the grain of one row per opportunity, with dimensions for Account, Owner (the sales rep), Company/Business Unit, and Date (close date and created date), plus a disconnected Pipeline Stage table for the funnel. Closed-won and closed-lost opportunities carry actual revenue; open opportunities carry estimated revenue weighted by a probability. Grain warning: stage history snapshots and current-stage facts are different models — do not average probabilities across stages without a clear definition of when probability is stamped.

Revenue trend uses time intelligence on closed-won actuals. A Moving Annual Total measure is the cleanest expression of run-rate: CALCULATE the revenue measure inside a DATESINPERIOD filter on the Date dimension for the trailing twelve months. Add a period-over-period comparison measure that shifts the same CALCULATE back a year, so every chart can show actual against last year and against quota. Quota as a separate disconnected table lets you overlay target lines without polluting the fact.

Visuals: a funnel of opportunity count and value by stage, a revenue-and-quota line chart with a prior-year comparison, and a leaderboard of owners by weighted pipeline and by win rate. If revenue and bookings data also live in F&O or BC, bring those fact tables into the same model under a shared Date dimension so the dashboard reconciles pipeline to booked revenue — the reconciliation gap is often the most valuable insight on the page.

Procure-to-pay spend dashboard

The spend dashboard answers 'where is our money going, to whom, and are we paying on terms?' Source it from vendor invoices and purchase receipts: in F&O the Vendor invoice and Purchase order entities, in BC the Vendor Ledger Entries and Purchase Lines. The model is a Spend fact at the grain of invoice line, with Vendor, Item/Category, Buyer, Company, and Date dimensions.

Add an on-time delivery measure from the receipts side — purchase lines received on or before the requested receipt date divided by all received lines — because spend without delivery quality leads to expediting costs that never show up in the invoice fact. Together these measures let procurement negotiate from data rather than anecdotes: vendor concentration for leverage, DPO for cash terms, on-time delivery for reliability.

Grain warning: credit memos and invoice cancellations must reverse spend in the same categories or vendor concentration and DPO drift. Multi-company consolidations need a common currency conversion date rule. Visuals: a Pareto chart of spend by vendor showing the cumulative share so the 20 percent of vendors driving 80 percent of spend is obvious, a DPO trend against terms, and a vendor scorecard table combining spend, on-time delivery, and on-time payment. Refresh daily; month-end close benefits from an extra scheduled refresh.

Refresh, storage mode, and capacity per recipe

Each recipe has a different freshness requirement, and matching refresh strategy to that requirement is what keeps a dashboard both current and cheap. Cash flow and AR aging move daily; inventory availability moves with every posting; revenue pipeline is fine on a daily or twice-daily cadence. Configure scheduled refresh to the lowest frequency the business actually consumes, not the highest the license allows.

The license tier sets the ceiling. Power BI Pro permits up to eight scheduled refreshes per day per semantic model; Premium per user, Premium capacity, and Microsoft Fabric capacities raise that to up to forty-eight refreshes per day. If a recipe genuinely needs intraday freshness beyond that, the answer is not more scheduled refreshes — it is incremental refresh, which partitions the model so only the rolling window of recent data is queried and loaded on each run while historical partitions are left untouched. On Fabric, Direct Lake can reduce the need for heavy Import refreshes when facts already live as Delta/parquet in OneLake.

Configure incremental refresh with two DateTime parameters named RangeStart and RangeEnd (these names are case-sensitive and reserved), and set the rolling-window and historical-period lengths on the policy. Turn on 'Detect data changes' against a last-modified column so only rows that actually changed are refreshed, and keep 'Only refresh complete days' on to preserve period integrity. For recipes that need true real-time — a live cash position, an operational shortage list — use a hybrid table that holds history in Import partitions and the most recent window in a DirectQuery partition; this requires a Premium or Fabric capacity.

Dataverse-backed recipes (the sales pipeline) have one more constraint: the TDS endpoint caps each query at roughly 80 MB. Push every filter you can into Power Query — date ranges, statuses, column selection — before data reaches the model, or month-end queries will fail. This is the most common reason a DirectQuery Dataverse dashboard feels fast in dev and dies in production. Capacity planning tip from the field: Fabric F-SKU compute is shared across engineering and BI workloads — data agents and heavy notebook jobs can burn capacity units quickly, so pin ERP semantic models to workspaces with capacity headroom before month-end close.

Embedding recipes and multi-entity RLS

A dashboard that lives only on PowerBI.com gets opened less than one that lives inside the ERP screen people already use. Pinning the recipe back into Dynamics 365 is the step that turns a report into behaviour. In F&O, an admin does a one-time PowerBI.com configuration under System Administration that registers an Entra ID app and lets workspaces pin reports and tiles; users then see the dashboard inside F&O without leaving the app. In Business Central, you pin Power BI report parts to a Role Center so the cash position or aging summary is the first thing a finance user sees on login.

Row-level security makes the same dashboard serve many users and many legal entities. Define roles in the semantic model keyed to the D365 user, team, or company — a collector sees only their assigned customers, a sales rep sees only their opportunities and accounts, a buyer sees only their categories, a country controller sees only their companies. Power BI applies that filter at query time. Without RLS you either build a separate dashboard per audience (unmaintainable) or expose everyone to everyone's numbers (unsafe).

Multi-entity ERP pattern: put Company (or Legal Entity / DataAreaId) on every fact and on the User Access bridge table (UserPrincipalName → Company, optionally → Salesperson, Buyer, or Customer group). RLS filters use USERPRINCIPALNAME() (or USERNAME() in limited scenarios) against that bridge, never hard-coded emails in DAX. For consolidated group views, grant a separate 'Group Finance' role that sees all companies, and keep entity-level roles for local controllers. Test RLS with 'View as' for at least one user per role before go-live — broken RLS is worse than no RLS because it looks correct until an auditor opens it.

Prefer pinning a single, well-designed report page over scattering tiles. A pinned page keeps the visual relationships intact — the cash line next to the waterfall next to the exposure table — so the dashboard tells one story. Tiles pulled out of context tend to multiply into noise on a Role Center. Treat the Role Center real estate as a premium surface: one headline page per audience, refreshed to the cadence above. When embedding, remember that viewers still need an appropriate Power BI license or Fabric capacity that covers free-user consumption, and Entra app registration scopes must match the workspace.

Optional: act inside the report (write-back patterns)

Most ERP dashboards should stay read-only: the system of record remains Dynamics 365, and the semantic model is a governed analytical layer. Treat write-back as an advanced, optional pattern for teams already on Microsoft Fabric capacity — not a baseline requirement for cash, AR, inventory, pipeline, or spend recipes.

Translytical task flows in Power BI let report authors wire buttons to Fabric user data functions so end users can add, edit, or delete records in Fabric SQL databases, warehouses, or lakehouse files, call external APIs, or surface in-report notifications — without leaving the report. Microsoft documents finance-friendly scenarios such as annotations, status updates, and approval requests that post to Teams from the filter context of the report. For most write-back work, Fabric SQL database is the recommended store because of OLTP-friendly read/write behaviour.

Practical guardrails for ERP teams: never write back to production F&O or BC ledgers from a dashboard without a deliberate integration design; prefer writing annotations, forecast overrides, or collection call outcomes into a side table that joins the semantic model. Keep audit columns (who, when, before/after). If you only need lightweight action, Power Automate from a Power BI button or a Dynamics form is still the lower-risk path. Use translytical flows when the value is keeping finance users inside the report for collaborative planning comments or operational status — not as a substitute for ERP data entry.

The deployment checklist for one recipe

Shipping a recipe safely is a short, repeatable sequence. Build the semantic model against a non-production environment first — a BC sandbox mirrors production data and lets you validate parameter behaviour and refresh timing without risking the live model, and F&O UAT environments do the same for Entity store, Data Lake, and Fabric Link work. Confirm the Environment and Company (or environment URL) parameters resolve, because a case-sensitive Company mismatch is the most common cause of a silent 'no data' refresh.

Validate three things before promoting: totals reconcile to the source report (the cash balance equals the trial balance; AR outstanding equals the aging report), the refresh completes inside its window with incremental refresh enabled, and the headline measures compute for an empty period without erroring (a divide-by-zero in DPO or turnover when COGS is zero is a classic). Wrap divisions in DIVIDE so a quiet week does not break the dashboard.

Promote to the production workspace, apply and test row-level security (including multi-company roles), pin the page to the relevant Role Centers or F&O workspaces, and document the refresh schedule and owner. Set up refresh-failure notifications on the semantic model so a broken scheduled refresh is caught the same day, not at month-end. If you use deployment pipelines (Premium/Fabric), promote Dev → Test → Prod as a unit so measures and RLS travel together. Then schedule the first review: a dashboard nobody reviews weekly becomes wallpaper, and the recipe only earns its keep if the team acts on it.

Frequently asked questions