How to Create an Aging Report in Power BI

An aging report is your financial early warning system, showing you exactly how much money customers owe you and, crucially, how long they’ve owed it. Creating one in Power BI transforms a static list of numbers into a dynamic, interactive dashboard for managing cash flow. This article will walk you through the entire process, step-by-step, from formatting your data and writing the necessary DAX formulas to building valuable, easy-to-read visuals.

What is an Accounts Receivable Aging Report?

An accounts receivable (AR) aging report is a financial document that categorizes unpaid customer invoices by their due dates. The purpose is to give you a clear, immediate understanding of your company's accounts receivable and the financial health of your customers. Think of it as a snapshot of who owes you money and how long those payments have been outstanding.

The report typically breaks down receivables into time-based "buckets," such as:

• Current: Invoices that are not yet due.
• 1-30 Days Overdue: Invoices that are 1 to 30 days past their due date.
• 31-60 Days Overdue: Invoices that are 31 to 60 days past their due date.
• 61-90 Days Overdue: Invoices that are 61 to 90 days past their due date.
• 91+ Days Overdue: Invoices that are more than 90 days past their due date.

By tracking this, you can quickly spot potential collection problems, manage your cash flow more effectively, and decide when to escalate collection efforts. A well-organized aging report is a foundational tool for any business looking to maintain financial stability.

Preparing Your Data for Power BI

Before you can build anything in Power BI, your data needs to be clean, organized, and available. Garbage in, garbage out - this saying is especially true for financial reporting. A successful aging report relies on accurate source data.

At a minimum, your dataset (whether it's an Excel file, a Google Sheet, or a database table) should contain the following columns:

• Customer Name: To identify who owes you money.
• Invoice Number: A unique identifier for each invoice.
• Invoice Date: The date the invoice was issued.
• Due Date: The date the payment is due.
• Amount Outstanding: The remaining balance left to be paid on the invoice.
• Status: A column indicating if the invoice is "Paid" or "Unpaid."

Loading Your Data

Once your data is ready, you can load it into Power BI. The process is straightforward and works similarly for various data sources.

1. From the Home ribbon in Power BI Desktop, click on Get Data.
2. Select your data source (e.g., Excel workbook, SQL Server, Web).
3. Navigate to your file or enter your server details and select the appropriate table.
4. Click Load. If your data needs cleaning (e.g., changing column types, removing null values), click Transform Data to open the Power Query Editor first. For this report, ensure your Invoice Date and Due Date columns are formatted as a Date data type.

Creating the Core DAX Calculations

With your data loaded, it's time to add the "intelligence" to our report using DAX (Data Analysis Expressions). These formulas will help us calculate the age of each invoice and assign it to the correct aging bucket. We'll add these as new calculated columns in our data table.

To create a calculated column, navigate to the Data view (the grid icon on the left sidebar), select your table, and click New Column from the ribbon at the top.

Step 1: Calculate "Days Overdue"

First, we need to calculate how many days have passed since an invoice's due date. We'll use a simple formula that compares the invoice due date to today's date. The formula should also check that the invoice is still marked as "Unpaid" to ensure we're not aging invoices that have already been settled.

Days Overdue = 
IF(
    'Invoices'[Status] = "Unpaid",
    INT(TODAY() - 'Invoices'[Due Date]),
    0
)

Let's break this down:

• IF('Invoices'[Status] = "Unpaid", ...): This formula first checks if the invoice is still open.
• TODAY() - 'Invoices'[Due Date]: This part calculates the difference in days between the current date and the due date. If the result is positive, the invoice is overdue.
• INT(...): This function converts the result into a whole number, removing any time components.
• 0: If the status is not "Unpaid," the 'Days Overdue' is set to 0.

Step 2: Create the "Aging Bucket" Column

Now that we know how many days each invoice is overdue, we can group them into our aging buckets. For this, a SWITCH function is perfect. It's cleaner and easier to read than a series of nested IF statements.

Create another new column and use the following DAX formula:

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

This SWITCH function evaluates the "Days Overdue" for each row and assigns it to the first category that matches the criteria, from top to bottom. For example, if 'Days Overdue' is 25, it is less than 30, so it gets assigned to "1-30 Days." If it's 75, it fails the first two checks but passes the <= 90 check, so it's assigned to "61-90 Days." The final "91+ Days" acts as a catch-all for anything over 90 days.

Step 3: Create a Sorting Column (Optional but Recommended)

When you add the "Aging Bucket" column to a chart or table, Power BI will sort it alphabetically by default ("1-30 Days," "31-60 Days," "61-90 Days," "91+ Days,", "Current"). This isn't the logical order we want. To fix this, we'll create one more column to define the correct sort order explicitly.

Create a new column named "Aging Bucket Sort":

Aging Bucket Sort = 
SWITCH(
    TRUE(),
    'Invoices'[Aging Bucket] = "Current", 1,
    'Invoices'[Aging Bucket] = "1-30 Days", 2,
    'Invoices'[Aging Bucket] = "31-60 Days", 3,
    'Invoices'[Aging Bucket] = "61-90 Days", 4,
    'Invoices'[Aging Bucket] = "91+ Days", 5,
    6
)

Now, go back to the Data view, select the Aging Bucket column, go to the Column tools tab in the ribbon, and click Sort by column. Choose Aging Bucket Sort from the dropdown menu. This tells Power BI to use our numeric column whenever it sorts the text-based buckets.

Building Your Aging Report Visuals

With our calculations in place, it’s time for the fun part: visualizing the data. Navigate back to the Report view (the chart icon on the far left).

1. Create a Matrix for Detailed Insights

The matrix visual is the most common format for an aging report because it clearly lays out who owes what and in which bucket.

• Select the Matrix visual from the Visualizations pane.
• Drag Customer Name to the Rows field.
• Drag your newly created Aging Bucket to the Columns field.
• Drag your Amount Outstanding to the Values field. Power BI will automatically sum it up.

You now have a classic AR aging summary, pivot-table style. You can expand each customer to see individual invoices if you add Invoice Number under Customer Name in the Rows field.

2. Visualize the Totals with a Bar Chart

A bar chart provides an excellent high-level view of where your money is getting stuck. This can instantly draw attention to the most problematic buckets.

• Add a Stacked column chart to your report canvas.
• Drag Aging Bucket to the X-axis field.
• Drag Amount Outstanding to the Y-axis field.

You’ll see the total amount outstanding for each aging category, sorted in the logical order we defined earlier. You can use this chart to quickly see if most of your outstanding money is in the 91+ days category - a sign of potential working capital issues.

3. Use Cards for Key Metrics

Cards are ideal for displaying single, important numbers that need to be seen at a glance.

• Add two Card visuals to your report. For the first one, drag Amount Outstanding into the Fields well to show the total accounts receivable.
• For the second, you can create a simple measure to only show the overdue amount:

Total Overdue Amount = 
CALCULATE(
    SUM('Invoices'[Amount Outstanding]), 
    'Invoices'[Days Overdue] > 0
)

• Drag this new measure into your second card visual. Now you have a clear total for AR and a separate total for only what is past due.

4. Make It Interactive with Slicers

Power BI's real strength lies in its interactivity. Add a Slicer visual from the Visualizations pane. Drag Customer Name into the field well. Now you (or your stakeholders) can click on a customer's name to filter the entire report and see just their outstanding invoices. You could also add slicers for product lines, sales regions, or any other relevant dimension in your data.

Final Thoughts

Building an AR aging report in Power BI is a great example of turning raw data into an actionable decision-making tool. By preparing your data, using a few key DAX formulas for aging calculations, and choosing clear visuals, you can create a dynamic dashboard that helps you stay on top of your cash flow and collections.

While Power BI offers deep customization, sometimes the setup and learning curve for DAX can be demanding, especially when you just need quick answers. We created Graphed for exactly this reason. Instead of writing formulas, you can connect tools like QuickBooks, Stripe, and your CRM, and simply ask: "Show me an aging report of our outstanding invoices from QuickBooks, grouped by customer." Graphed generates an interactive, real-time dashboard in seconds, allowing your whole team to find answers without needing a data analyst.