Aged Receivables Reports for Xero in Power BI

Home / Report Templates / Aged Receivables Reports for Xero in Power BI

The Aged Receivables Reports for Xero in Power BI is a simple dashboard that allows you to report and reconcile money owed to you by your customers.

It reproduces much of the functionality available in the Aged Receivables Summary and Aged Receivables Detail report in Xero directly within Power BI.

This report can also consolidate data from multiple Xero Organisations (data files) when using the Consolidation OData Feed.

While this report a fully working and standalone report, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.

Also available are Aged Receivables Reports for Xero in Excel for those that prefer using Excel. There is also an Aged Payables Reports for Xero in Power BI and an an Aged Payables Reports for Xero in Excel which allows you to reconcile your outstanding supplier invoices.

Reports Includes

Consolidated Receivables Summary

This report provides the aged receivables summary for multiple data files when using a Consolidation OData Feed. Each company will be listed separately down the page.

Receivables Summary by Months

This report provides the aged receivables summary for all customers.

Receivables Detail by Months

This report provides the aged receivables detail for all customers. It lists all customer invoices outstanding.

Receivables Detail by Months (%)

This report provides the aged receivables detail for all customers. Rather than showing the dollar value, it instead shows the percentage split of the outstanding amount.

Receivables Summary by Days

This report provides the aged receivables summary for all customers. This report is similar to the Receivables Summary by Months but instead breaks the categories down as 0, 30, 60, 90 days bucket.

Receivables Detail by Days

This report provides the aged receivables detail for all customers. This report is similar to the Receivables Detail by Months but instead breaks the categories down as 0, 30, 60, 90 days bucket.

Requirements

To use the report, you will need to:

  1. Have an OdataLink Subscription or Trial Account.
  2. Connect OdataLink to your Xero Organisation.
  3. Configure a Model that includes the following endpoints:

Download and Setup

We provide 2 versions of the reports to download.

Aged Receivables Reports For Xero
This version of the report allows you to report on your receivables as at any date required. This does come at a small increase in performance as additional data must be downloaded.
Download the Aged Receivables Reports For Xero

Aged Receivables Reports For Xero (Current)
This version of the report allows you to report on your receivables as of right now. It only looks at currently outstanding invoices and as such does not need to download a lot of information out of Xero.
Download the Aged Receivables Reports For Xero (Current)

Once downloaded, you can connect it to your own data using this article.

How to Refresh in Power BI Desktop

To refresh it in Power BI Desktop, follow these steps.

  1. Under the Home tab, go to Transform Data and click on Edit Parameters
  2. Enter the date for which to calculate the age.
    (this parameter does not apply to the Aged Receivables Reports For Xero (Current) report)
  3. Choose whether to calculate the age using the Invoice Date or the Due Date.
  4. Click the Refresh button and wait for the data to download.
  5. If using the consolidation OData Feed, a slicer is also available allow you to select one or more company or data file to report on.

Find an issue with the template?

Let us know of any issues you find with the template. We built it to use the least amount of data possible but may have this might not suit everyone.