How to Create an Accounts Payable Dashboard in Power BI
Building an Accounts Payable dashboard in Power BI is one of the quickest ways to get a handle on your company's cash flow and vendor relationships. This guide will walk you through the process, from identifying the right metrics to building the actual visualizations, step by step.
Why an Accounts Payable Dashboard Matters
If you're managing accounts payable in spreadsheets, you know the drill. Manually updating records, trying to figure out which invoices are overdue, and compiling reports is a slow, error-prone process. By the time you're done, the data is already out of date.
An interactive Power BI dashboard solves this. It gives you a real-time, bird’s-eye view of your AP process, helping you:
Visualize Cash Flow: Instantly see how much you owe, to whom, and when it’s due.
Avoid Late Fees: Proactively identify overdue and upcoming payments to maintain good vendor relationships and your credit score.
Spot Bottlenecks: Pinpoint delays in your invoice approval and payment process.
Negotiate Better Terms: Use data on payment history and volume to negotiate better discounts or terms with key vendors.
Key Metrics for Your AP Dashboard
A great dashboard tells a story. Before you build anything, you need to decide what story you want to tell. Here are the most important metrics to include in any accounts payable dashboard.
AP Aging Summary
This is the cornerstone of any AP report. It buckets your outstanding payables into categories based on how long they've been due. This immediately tells you where your most urgent liabilities lie.
Current (Not yet due)
1-30 days overdue
31-60 days overdue
61-90 days overdue
91+ days overdue
Days Payable Outstanding (DPO)
DPO measures the average number of days it takes for your company to pay its invoices. A high DPO might mean you're holding onto cash longer (which can be good for working capital), but it could also signal that you're paying vendors late, potentially straining relationships. Tracking this over time shows how efficiently you're managing your cash outflow.
Total Amount Owed by Vendor
A simple but powerful visual. This helps you quickly identify your largest creditors and understand where most of your committed cash is going. It's essential for managing key vendor relationships and analyzing financial risk concentration.
Invoice Processing Cycle Time
This metric tracks the average time from when you receive an invoice to when it's fully paid. A long cycle time can indicate inefficiencies in your internal approval process, leading to missed early payment discounts and strained vendor relations.
Early Payment Discounts Captured vs. Missed
Many vendors offer a discount (like 2/10, net 30) for paying early. This KPI tracks how much money you’re saving by taking advantage of these discounts versus how much you're leaving on the table. It’s a direct measure of your AP department’s efficiency and financial contribution.
Step-by-Step: Build Your AP Dashboard in Power BI
Now, let's get into the practical steps of building the dashboard. For this tutorial, we'll assume your data is in an Excel or CSV file, a common starting point for many businesses.
Step 1: Get Your Data Ready
Clean data is the foundation of a useful dashboard. Your source file should have, at a minimum, the following columns:
Vendor Name
Invoice ID
Invoice Date
Due Date
Invoice Amount
Payment Date (can be blank for unpaid invoices)
Amount Paid
Step 2: Connect and Transform Data in Power Query
Once your data is organized, open Power BI Desktop and connect to it.
Go to the Home tab and click on Get Data. Select Excel workbook or Text/CSV depending on your file type.
Navigate to your file and click Open. In the Navigator window, select your data table and click Transform Data. This opens the Power Query Editor, which is where the magic happens.
Inside the Power Query Editor, you'll clean and prepare your data for analysis. Here are a few essential transformations:
Check Data Types
Power Query is usually good at guessing data types, but you should always double-check. Ensure your date columns are set to the ‘Date’ type and your amount columns are ‘Decimal Number’ or ‘Fixed Decimal Number’.
Create Calculated Columns for Analysis
We need to add some custom logic to create our key metrics. We'll add columns for Invoice Status, Days Overdue, and AP Aging Buckets.
Go to the Add Column tab and select Custom Column or Conditional Column.
Invoice Status: A simple way to see if an invoice is paid or outstanding. Use a Conditional Column with this logic:
If [Amount Paid] is not null, then "Paid," else "Outstanding."Days Overdue: This calculation will show us how late an unpaid invoice is.
if [Invoice Status] = "Outstanding" and [Due Date] < Date.From(DateTime.LocalNow()) then Duration.Days(DateTime.LocalNow() - [Due Date]) else 0This formula checks if an invoice is outstanding and past its due date. If so, it calculates the difference in days between today and the due date.
AP Aging Bucket: This is the most important one. Use a Conditional Column to group invoices based on their Days Overdue.
If Days Overdue is greater than or equal to 91, then "91+ Days"
If Days Overdue is greater than or equal to 61, then "61-90 Days"
If Days Overdue is greater than or equal to 31, then "31-60 Days"
If Days Overdue is greater than or equal to 1, then "1-30 Days"
Else, "Current"
Once you've made these changes, click Close & Apply on the Home tab to load your prepared data into Power BI.
Step 3: Create DAX Measures for KPIs
Measures are calculations that you create using the DAX (Data Analysis Expressions) formula language. They let you aggregate data on the fly. In the Report view, right-click your table in the 'Data' pane and select New measure.
Total Outstanding Amount
This shows the total amount you still owe.
Number of Overdue Invoices
Step 4: Design Your Dashboard with Visuals
This is where you bring your data to life. Drag and drop visuals onto the canvas from the Visualizations pane and add your data fields.
KPI Cards:Use Card visuals for your main KPIs. Create cards for:
Total Outstanding (using your measure)
Overdue Invoices Count (using your measure)
Average Invoice Processing Cycle Time
AP Aging Chart:A Stacked column chart is perfect for this.
On the X-axis, drag your AP Aging Bucket column.
On the Y-axis, drag your Invoice Amount field.
To make it more useful, reorder the buckets logically by clicking the three dots on the chart, selecting Sort Axis, and sorting by 'AP Aging Bucket'. You may need to create a custom sort order for this to display correctly (Current → 1-30 Days → etc.).
Outstanding Amount by Vendor:Use a Treemap or a horizontal Bar chart to see this at a glance.
For the treemap, use Vendor Name for the Category and Invoice Amount for the Values.
Filter this visual by "Invoice Status is Outstanding" in the Filters pane.
Detailed Invoice Table:Add a Table visual to see the raw data. Include columns like Vendor Name, Invoice ID, Due Date, Days Overdue, and Invoice Amount. Use conditional formatting on the 'Days Overdue' column to color-code urgent invoices in red.
Step 5: Add Interactivity with Slicers
Slicers make your dashboard interactive. Add a Slicer visual and drag Vendor Name into it. Now you can filter the entire dashboard to see the data for a specific vendor. You can also add a slicer for the Invoice Date to analyze performance over specific periods.
Final Thoughts
Creating an accounts payable dashboard in Power BI transforms a tedious manual process into a dynamic, insightful financial tool. By connecting your data and setting up the right visuals, you gain immediate, actionable insights into your company’s financial health and vendor management efficiency.
The heaviest lift in this process is often the setup - connecting data sources, cleaning the data in Power Query, and writing DAX measures. This is what inspired us to create Graphed. We envisioned a tool where you could connect your platforms like QuickBooks or your spreadsheets and skip the manual build entirely. Instead of struggling with formulas, you can simply ask, "create an AP aging dashboard showing outstanding invoices by vendor and due date buckets," and get a live, interactive report in seconds, not hours.