How to Create an AR Aging Report in Power BI

Tracking your Accounts Receivable (AR) is essential for managing cash flow, but shuffling through spreadsheets is a slow and painful way to see who owes you money. With an AR Aging report in Power BI, you can turn that static list of invoices into a dynamic, interactive dashboard that instantly tells you where your money is. This tutorial will walk you through the entire process, step-by-step, from loading your data to creating the aging buckets and visualizing the results.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is an AR Aging Report?

An Accounts Receivable (AR) Aging report is a critical financial tool that categorizes unpaid invoices by the length of time they have been outstanding. Instead of just seeing a total amount owed, you see that total broken down into time-based "buckets" like:

• Current: Invoices that aren't yet due.
• 1-30 Days Past Due: Invoices overdue by one month.
• 31-60 Days Past Due: Invoices overdue by one to two months.
• 61-90 Days Past Due: Invoices overdue by two to three months.
• 91+ Days Past Due: Severely overdue invoices.

The main benefit of this report is clarity. It helps you quickly identify which customers are late to pay, assess potential credit risks, and focus your collection efforts where they’ll have the most impact. A well-built Power BI dashboard makes this process even easier by allowing you to filter, drill down, and see the health of your cash flow at a glance.

Step 1: Get Your Data Ready

Before you even open Power BI, you need clean, well-structured source data. Most modern accounting software can export this for you, but you can also pull it from a database or even a well-managed Excel or Google Sheets file. The better your source data, the easier the next steps will be.

Make sure your data has, at a minimum, the following columns:

• Invoice Number: A unique identifier for each invoice.
• Customer Name: The name of the client who owes you money.
• Invoice Date: The date the invoice was issued.
• Due Date: The date the payment is expected.
• Invoice Amount: The total amount of the invoice.
• Amount Paid: The amount that has been paid so far (this will be zero for unpaid invoices).

From these columns, you can easily calculate the outstanding balance by subtracting Amount Paid from Invoice Amount. Most systems will provide this for you, but if not, it’s a simple calculation you can do in Power Query.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Load Your Data into Power BI

With your data source prepared, it’s time to bring it into Power BI Desktop.

1. Open a new Power BI report.
2. From the Home ribbon, click on Get Data and select the appropriate source (e.g., "Excel workbook," "Text/CSV," "SQL Server").
3. Navigate to your file or connect to your database and select the table containing your AR data.
4. When the Navigator window appears, click Transform Data instead of Load. This opens the Power Query Editor, which is where we’ll do some initial data cleaning and preparation.

Always inspect your columns in the Power Query Editor. Make sure Power BI has correctly identified the data types — for instance, ensure your Invoice Date and Due Date columns are set to a "Date" type, and your amount columns are set to "Decimal Number" or "Fixed decimal number." You can change data types by clicking the icon on the left side of the column header.

Step 3: Calculate 'Days Overdue' in Power Query

The core of an AR aging report is understanding how long each invoice has been outstanding. We'll create a new column to calculate this. In Power BI, you can easily compare any date to "today's" date.

With your table open in the Power Query Editor:

1. Navigate to the Add Column tab in the ribbon.
2. Click on Custom Column.
3. A new window will open. Name your new column Days Overdue.
4. In the formula box, we’ll write a small piece of M code. The goal is to get the number of days between the Due Date and today. Paste the following formula:

Duration.Days(DateTime.Date(DateTime.LocalNow()) - [DueDate])

Here’s a breakdown:

• DateTime.LocalNow() gets the current date and time.
• DateTime.Date(...) converts that to just the date (ignoring the time of day).
• The formula then subtracts your [DueDate] from today's date. Make sure the column name [DueDate] exactly matches your column name.
• Duration.Days(...) converts the resulting duration into a number of days.

After clicking OK, you'll see your new Days Overdue column. If an invoice isn't due yet, this number will be negative. Finally, select the new column and, in the Transform tab, change its Data Type to Whole Number.

Once this is done, go to the Home tab and click Close & Apply to load your prepared data into the Power BI model.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Create the Aging Buckets with DAX

Now that our data is loaded, we need to categorize each invoice into the aging buckets we defined earlier. The best way to do this is with a calculated column using DAX.

1. In Power BI Desktop, click on the Data View icon on the left-hand navigation bar (it looks like a table).
2. Select your AR table from the Fields pane on the right.
3. From the Table tools ribbon, click on New column.
4. The DAX formula bar will appear. Here, we'll write a SWITCH statement to create our buckets. This function checks a condition and returns a value when it finds the first TRUE condition. Paste in the following DAX formula (be sure to replace 'YourTableName' with the actual name of your table):

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

Press Enter. Power BI will evaluate each row and assign the invoice to the correct aging bucket. Now you have a clean, easy-to-use category for all your visualizations.

Pro Tip: Sort Your Aging Buckets Correctly

One common problem is that when you put the "Aging Bucket" column into a chart, it sorts alphabetically (e.g., "1-30 Days," "31-60 Days," "61-90 Days," "91+ Days," "Current"). This is not logical. To fix this, create a sort-order column.

1. Create another new calculated column (just like before). Name it Aging Priority.
2. Paste in this DAX formula, which assigns a number to each bucket for sorting:

Aging Priority = 
SWITCH(
    TRUE(),
    'YourTableName'[Aging Bucket] = "Current", 1,
    'YourTableName'[Aging Bucket] = "1-30 Days", 2,
    'YourTableName'[Aging Bucket] = "31-60 Days", 3,
    'YourTableName'[Aging Bucket] = "61-90 Days", 4,
    5
)

1. Once the Aging Priority column is created, select the Aging Bucket column in the Fields list.
2. Go to the Column tools ribbon and click Sort by column.
3. Select Aging Priority. Now, whenever you use the Aging Bucket field, it will sort correctly.

Step 5: Build an Interactive Report

With the hard work done, you can now build the visualizations. Switch to the Report View on the left.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

A Matrix for Detailed Views

A matrix is perfect for a detailed breakdown. Select the Matrix visual from the Visualizations pane.

• Drag Customer Name to the Rows field.
• Drag your (now sorted) Aging Bucket to the Columns field.
• Drag the remaining Invoice Amount field to the Values field.

You'll now have a crystal clear pivot table showing which customers owe you what, perfectly categorized by how long the payments have been overdue.

Bar Charts for Big Picture Summary

To see your total outstanding AR by bucket, a bar chart is ideal. Select the Stacked column chart visual.

• Drag Aging Bucket to the X-axis field.
• Drag the Invoice Amount to the Y-axis field.

This gives you a powerful visual summary, showing where the majority of your outstanding cash is stuck. You can immediately see if your over-90-days bucket is growing too large.

KPI Cards

Finally, add some high-level metrics using the Card visual. Create cards to show:

• Total Outstanding A/R: Simply drag the Invoice Amount field into a Card visual.
• Total Overdue A/R: Create a new DAX Measure: Total Overdue = CALCULATE(SUM('YourTableName'[Invoice Amount]), 'YourTableName'[Days Overdue] > 0). Drag this measure into a card.

Final Thoughts

Creating an AR aging report in Power BI transforms a static spreadsheet into a dynamic tool for managing your company's financial health. With just a few transformations in Power Query and some straightforward DAX, you can build an automated, interactive report that saves you hours and provides immediate clarity on your cash flow.

While Power BI is an excellent tool for deep analysis, we've found that marketing, sales, and operations teams often need a faster way to get answers without learning the intricacies of DAX or data models. We built Graphed to solve exactly this problem. Instead of performing the steps above, you can connect your data sources and simply ask in plain English: "Create an accounts receivable aging report showing total outstanding balance per customer for the last quarter." It builds the dashboard for you in seconds, saving valuable time and empowering anyone on your team to make data-driven decisions.