Accounts receivable dashboards in Power BI: what to measure and how to build them
Which KPIs an AR and collections dashboard needs, how to model aging, which sources to connect (SQL Server, SAP, Siigo, World Office) and how to automate overdue alerts.
Receivables is the metric that gets expensive fastest when nobody watches it. A monthly Excel report arrives when the customer is already 70 days past due; a proper AR dashboard warns you on day 5.
Here is the structure we implement in Power BI for finance teams: the KPIs that actually get used, the minimum data model, and the alerts that turn a dashboard into collection action.
KPIs you cannot skip
- ▸Total receivables and aging buckets: current, 1-30, 31-60, 61-90, over 90 days.
- ▸DSO: average days it takes a sale to become cash.
- ▸% overdue over total receivables and over period sales.
- ▸Collections for the period vs target and vs same month last year.
- ▸Concentration: how much of your AR depends on the top 10 customers.
- ▸Receivables by sales rep and channel, to close the loop with sales.
- ▸Collection effectiveness: payment promises kept over promises logged.
- ▸Estimated impairment provision based on historical behavior.
Minimum data model
An AR dashboard rests on a simple star schema. Without it, aging measures become unmaintainable.
- ▸Document fact table: invoices, credit notes, receipts, advances, with issue date, due date, amount and balance.
- ▸Calendar dimension marked as a date table (mandatory for time intelligence).
- ▸Customer dimension with segment, city, payment terms and assigned rep.
- ▸Document and Status dimensions to separate overdue, upcoming and disputed.
- ▸DAX measures for balance as of date, aging buckets, DSO and cumulative collections.
Where the data comes from
The right source depends on your accounting ERP. These are the scenarios we handle most often:
| Source | How it connects | Consideration |
|---|---|---|
| SQL Server / PostgreSQL / Oracle | Direct connection or dedicated view | Best case: incremental refresh, no production locking |
| Siigo / World Office / Alegra | API or automated exports | Normalize third parties and document numbering |
| SAP / Dynamics / Odoo | Views, OData or API | Agree the accounting cut-off with finance |
| Excel and Google Sheets | Folder or Sheets source | Only for data that doesn't live in the ERP |
| Azure / AWS / Google Cloud | Intermediate data warehouse | Recommended for multi-company or multi-currency |
How to build aging that ties to the ledger
Aging must reconstruct the balance as of the cut-off date, not take today's balance. That means summing documents issued up to the date and subtracting payments applied up to that same date, honoring credit notes and advances.
When the dashboard doesn't tie to the balance sheet, 90% of the time it's one of three causes: payments applied to another entity's documents, unmatched credit notes, or differences between document date and posting date. That reconciliation is agreed with the accountant before publishing.
Alerts and automation
- ▸Daily email subscription with customers crossing 30, 60 and 90 days.
- ▸Alert to the sales rep when their customer becomes overdue.
- ▸WhatsApp notification to the collections team with the daily list.
- ▸Scheduled refresh several times a day during business hours.
- ▸Role-level security: each rep sees their own book; management sees everything.
Common build mistakes
- ▸A dashboard with 40 visuals nobody uses: start with one executive page and one detail page.
- ▸Not marking the date table, then fighting time intelligence measures.
- ▸Showing only total receivables with no aging or trend: it drives no action.
- ▸Leaving the file on one person's laptop instead of publishing it with scheduled refresh.
Frequently asked questions
Does it work if my accounting runs on Siigo or World Office?+
Yes. Data is extracted via API or automated exports, third parties and documents are normalized, and the dashboard refreshes without manual work.
How long until the first AR dashboard?+
With data access, a productive version with aging, DSO and collections usually takes 2 to 4 weeks, including reconciliation with accounting.
Do I need Power BI licenses for everyone?+
It depends on distribution. You can publish to the service with per-user licenses, embed the report in your own web application, or send scheduled reports by email.
Can I see receivables across several companies?+
Yes, one of the most requested cases. It is consolidated in a multi-company model with harmonized currency and chart of accounts, filterable by business unit.
Keep reading
Sources and references
See an AR dashboard with sample data
In 30 minutes you'll see aging, DSO and alerts working, and we'll tell you what it takes to connect it to your accounting system.