How to Create an AR Aging Report in Power BI with AI

Creating an accounts receivable (AR) aging report is a fundamental task for tracking financial health, but it often involves manual data wrangling in spreadsheets that are outdated the moment you finish them. Turning this static report into a dynamic dashboard in Power BI not only saves time but also unlocks deeper insights. This guide will walk you through building a standard AR aging report in Power BI and then show you how to enhance it using the platform's built-in AI features to get answers faster.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is an Accounts Receivable Aging Report?

An accounts receivable aging report is a financial summary that categorizes unpaid customer invoices by the length of time they have been outstanding. Its main purpose is to help you manage cash flow by quickly identifying overdue payments and potential credit risks. It’s one of the most effective tools for a collections team to prioritize their efforts.

It’s one of the most effective tools for a collections team to prioritize their efforts.

Instead of just a single "total receivables" number, an aging report breaks it down into time-based buckets. The most common aging buckets are:

• Current: Invoices that are not yet due (typically 0-30 days from the invoice date).
• 1-30 Days Overdue: Invoices that are 1 to 30 days past their due date.
• 31-60 Days Overdue: Invoices that are anywhere from 31 to 60 days past due.
• 61-90 Days Overdue: Invoices between 61 and 90 days past due.
• 91+ Days Overdue: The most critical category, representing invoices that are more than 90 days past due.

By monitoring these buckets, you can spot trends, such as a customer who consistently pays late or a sudden increase in the "91+ Days" category, allowing you to act quickly to improve your collection process and maintain healthy cash flow.

Step 1: Preparing Your Data

Before you can build anything in Power BI, you need clean, well-structured data. For an AR aging report, you'll typically export data from your accounting software (like QuickBooks, Xero, or an enterprise ERP) into an Excel or CSV file. The cleaner your source data, the easier your job will be in Power BI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Key Data Fields You'll Need

Ensure your exported file contains at least the following columns. The names don't have to be identical, but the information must be present.

• Invoice Number: A unique identifier for each invoice.
• Customer Name: The name of the client who owes the money.
• Invoice Date: The date the invoice was issued.
• Due Date: The date the payment is due. This is crucial for our calculations.
• Invoice Amount: The total amount of the invoice.
• Amount Paid: The amount that has already been paid.
• Remaining Balance: The outstanding amount. This can also be a calculated field (Invoice Amount - Amount Paid).

Once you have this file, keep it somewhere easily accessible. Before importing, scan through it to check for obvious errors like blank due dates, zeros in the invoice amount, or inconsistent formatting. While you can clean data in Power BI's Power Query Editor, a quick check upfront can save you a headache later.

Step 2: Building the Core Report in Power BI

With your data file ready, it's time to open Power BI Desktop and start building the report. We'll start by importing the data, then use DAX (Data Analysis Expressions) to create the logic for our aging buckets.

Importing Your Data

First, connect your data to Power BI:

1. Open Power BI Desktop.
2. From the Home ribbon, click on Get data.
3. Select Excel workbook or Text/CSV depending on your file type and locate your exported accounts receivable file.
4. In the Navigator window that appears, select the sheet or table containing your data and click Load. If your data needs cleaning (e.g., changing data types, removing columns), click Transform Data to open the Power Query Editor first.

Creating the "Days Overdue" Calculation

The foundation of the aging report is calculating how many days each invoice is overdue. We accomplish this by creating a calculated column using DAX. A calculated column computes a value for each row in your table and stores it as a new column.

1. Navigate to the Data view (the grid icon on the left pane).
2. Select the table containing your invoice data.
3. From the Table tools ribbon, click New column.
4. Enter the following DAX formula into the formula bar and press Enter:

Days Overdue =
VAR TodayDate = TODAY()
VAR DueDate = 'Invoices'[Due Date]
RETURN
IF(
    'Invoices'[Remaining Balance] > 0,
    INT(TodayDate - DueDate),
    BLANK()
)

Breaking Down the Formula:

• VAR TodayDate = TODAY(): This creates a variable called TodayDate that stores the current date. Using TODAY() ensures your report is always up-to-date.
• IF('Invoices'[Remaining Balance] > 0, ... ): This is a critical check. We only want to calculate "Days Overdue" for invoices that still have an outstanding balance. If an invoice is fully paid, there's no need to age it.
• INT(TodayDate - DueDate): If the balance is greater than zero, this calculates the difference between today and the invoice's due date. We use INT() to get a whole number. A positive result means the invoice is overdue, while a negative means it isn't due yet.
• BLANK(): If the remaining balance is zero, the calculation returns a blank, effectively ignoring paid invoices.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Creating the Aging Buckets

Now that we have the "Days Overdue" for each invoice, we can categorize them into our aging buckets. We'll create another calculated column for this.

1. With your table still selected in the Data view, click New column again.
2. Enter the following DAX formula:

Aging Bucket =
VAR DaysOverdue = 'Invoices'[Days Overdue]
RETURN
IF(
    ISBLANK(DaysOverdue),
    "Paid",
    SWITCH(
        TRUE(),
        DaysOverdue <= 0, "Current",
        DaysOverdue <= 30, "1-30 Days",
        DaysOverdue <= 60, "31-60 Days",
        DaysOverdue <= 90, "61-90 Days",
        "91+ Days"
    )
)

Breaking Down the Formula:

• SWITCH(TRUE(), ...): This is an efficient way to write a series of if-then-else conditions. It evaluates each condition in order and stops at the first one that is true.
• DaysOverdue <= 0, "Current": If the "Days Overdue" value is zero or negative, the invoice is categorized as "Current."
• DaysOverdue <= 30, "1-30 Days": If the first condition isn't met, it checks if the value is less than or equal to 30. This catches everything from 1 to 30.
• The pattern continues for 60 and 90 days buckets.
• "91+ Days": This is the final catch-all. If none of the previous conditions are true, the invoice must be more than 90 days overdue.

Visualizing the AR Aging Data

With your calculations complete, switch to the Report view (the bar chart icon). Now you can build visuals.

• The Classic Matrix View: The most common AR aging visual. Drag a Matrix visual onto the canvas. Set it up with Customer Name on Rows, Aging Bucket on Columns, and Sum of Remaining Balance on Values. This gives you a clear, detailed breakdown by customer.
• Stacked Bar Chart: For a high-level overview, use a Stacked Bar Chart. Put Aging Bucket on the X-axis and Sum of Remaining Balance on the Y-axis. This quickly shows you the total amount in each category.
• Donut Chart: To see the proportion of funds in each bucket, use a Donut Chart. Set the Legend to Aging Bucket and Values to Sum of Remaining Balance.
• Slicers: Add Slicers to your report to make it interactive. You can create slicers for Customer Name or salespeople to allow users to filter the entire report quickly.

Step 3: Taking Your Report to the Next Level with AI

A standard AR report is good, but Power BI’s built-in AI features can transform it into an analytical powerhouse, helping you uncover insights you might have otherwise missed.

Using the Q&A Visual for Natural Language Queries

The Q&A (Question and Answer) visual empowers any user on your team to "talk" to your data. Instead of dragging and dropping fields, they can just type questions in plain English.

Drop a Q&A visual onto your report canvas. Now, users can ask questions like:

• "what is the total remaining balance for XYZ Corp"
• "show me a list of all invoices over 90 days overdue"
• "compare remaining balance by customer as a bar chart"

This lowers the barrier to entry, allowing finance managers, salespeople, and executives to get quick answers without needing to learn the Power BI interface.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Finding the "Why" with Analyze Features

Have you ever seen a number in a report spike and wondered why? Power BI's "Analyze" feature can find the answer for you.

On a bar chart showing receivables by aging bucket, you might notice that the "91+ Days" bar has doubled since last quarter. Instead of manually digging through tables, you can simply right-click that bar and select Analyze > Explain the increase. Power BI's AI will automatically search through your dataset to identify the main contributors - for example, it might surface that a single, large invoice from one customer makes up 85% of that increase. It's a huge time-saver for diagnostic analytics.

Identifying Outliers and Anomalies

If you create a line chart showing the total overdue amount over time, you can use Power BI’s anomaly detection feature. In the Analytics pane for the visual, just turn on “Find anomalies.” Power BI will then automatically highlight any data points that are statistically unusual based on the historical pattern. It can detect a sudden, unexpected spike in overdue receivables in a specific month, allowing you to investigate the root cause immediately instead of discovering it weeks later.

Final Thoughts

Building an accounts receivable aging report in Power BI moves your financial reporting from a slow, manual process to a dynamic, always-on system. By using DAX to create calculated columns for "Days Overdue" and "Aging Bucket," you create a strong foundation that can be used to build insightful visualizations that help manage cash flow and reduce credit risk.

While the steps above provide massive value, learning DAX and navigating the Power BI interface still requires time and effort. We built Graphed to remove this friction entirely. Once you connect your data sources, you can ask in plain English, "create a dashboard showing my AR aging by customer," and get a live, interactive report in seconds. There's no DAX to write or visuals to configure - our AI handles all the background work so you can get straight to the insights.