Invoices

Full details on the Invoice data available in Microsoft Power BI from Xero with Connectorly.

Nick Maxwell avatar
Written by Nick Maxwell
Updated over a week ago

Overview

The Invoices table contains both Sales and Bills (aka Purchase) Invoices, as well as any respective credit notes.

Amounts are signed so that they can be summed to create totals. Positive amounts are income and negative amounts are used for expenses.

Invoices provide a rich view of the financial transactions with Customers and Suppliers but because they don't hold all financial transactions (for example Salary and Tax) you will not see all financial data working with Invoices alone.

Invoice Amounts

To make reporting easier and provide access to Invoice line descriptions, product codes and amounts without needing to switch between an invoice header record and invoice details, a record is returned for every Invoice Line of the Invoice.

The Invoice Due, Credited, Paid, Net, Remaining, Gross, Tax and their reporting currency equivalents, are split proportionately for the Invoice across all Invoice lines.

This may not be immediately obvious in Power BI if, for example, you pull out the Net Amount and Invoice Number, because Power BI automatically sum amounts.

No need to worry about Tax Exclusive or Inclusive

If you've previously worked with raw Xero data, you'll have had to cope with the conversion of the two different Invoice representations; either exclusive or tax or inclusive.

Don't worry. We've done that so you don't need to.

Forecasting with Repeating Invoices

To simplify the forecasting of future sales or bills, Connectorly automatically creates Invoice records for any repeating invoices according to their schedule that is yet to be created, up to three years in the future (or until the end date that is set).

These invoices are distinguished by a Status of FORECAST, which joins the Xero values of AUTHORISED, DRAFT, PAID and SUBMITTED.

Multi Currency Xero Support

As well as providing the Net, Tax and Gross values of the original Invoice or Credit Note, values for your chosen Reporting Currency are calculated. These are signified by (RC) after their name.

If you are working with Xero data from multiple organisations or even from a single multi-currency Xero, use the Reporting Currency (RC) values for any calculations, to avoid adding amounts that are in different currencies.

Data Model

Field

Type

Notes

Account Code

Text

The Account Code for the invoice line.

e.g. 200

Account Name

Text

The corresponding Account Name.

e.g. Sales

If want to report and link other sources of financial information (for example to link Budgets and Invoices data to compare actuals and forecasts) then use the linked Account Name field from the Accounts table.

Amount Credited

Number

The Amount of any credit notes applied to the invoice in the currency the Invoice was transacted in.

Amount Credited (RC)

Number

The Amount of any credit notes applied to the invoice in the reporting currency.

Amount Due

Number

The Amount still due in the currency the Invoice was transacted in.

Amount Due (RC)

Number

The Amount still due in the in the reporting currency.

Amount Paid

Number

The Amount paid in the currency the Invoice was transacted in.

Amount Paid (RC)

Number

The Amount paid in the in the reporting currency.

Connection Name

Text

The name of the Connection. Typically the name of the Xero Organisation.

Contact ID

Text

The primary key for the Contact.

Contact Name

Text

The name of the Contact (Supplier or Customer).

Currency Rate

Number

The FX Rate used to calculate the Reporting Currency Amounts. Rates are taken as the closing rates as reporting by the ECB on the invoice date.

Data current as of

Datetime

When the Data was taken from Xero.

Description

Text

The Invoice Line Description.

Due Date

Date

The date the Invoice is due for Payment by.

Expected Payment Date

Date

Uses the Xero Planned Payment Date if one exists. If not it is calculated based on the Average Days to pay for the Contact or if this is the first invoice or bill, the Due Date.

Fully Paid On Date

Date

The date on which the Invoice was fully paid.

Gross Amount

Number

The Gross Amount (Net and Tax) in the currency the Invoice was transacted in.

Gross Amount (RC)

Number

The Gross Amount (Net and Tax) in the reporting currency.

Has Attachments

Logical

Invoice Date

Date

The date of the invoice. Also known as the Tax Point date.

Invoice Number

Text

Invoice Type

Text

From Xero.

{ACCPAY', 'ACCPAYCREDIT', 'ACCREC', 'ACCRECCREDIT}

ACCPAY are Bills (or Purchase) Invoices

ACCREC are Sales Invoices.

Types ending CREDIT are Credit notes for the respective prefixes.

Is Discounted

Logical

Last Updated

Datetime

When the Invoice was created or updated last.

Net Amount

Number

The Net (without Tax) amount of the Invoice in the currency it was transacted in.

Net Amount (RC)

Number

The Net (without Tax) amount in the reporting currency.

Past Due Interval

Duration

How long past the Due date the invoice remains unpaid.

Planned Payment Date

Date

The date, entered in Xero, when an Invoice or Bill is Planned for Payment.

Product Code

Text

Quantity

Number

The number of items on the Invoice Line Item.

Reference

Text

The Invoice Reference often holds a Purchase Order number.

Remaining Past 0d

Number

An Invoice with an amount that is past the Due Date.

Remaining Past 30d

Number

An Invoice with an amount that is 30 days past the Due Date.

Remaining Past 60d

Number

An Invoice with an amount that is 60 days past the Due Date.

Remaining Past 90d

Number

An Invoice with an amount that is 90 days or more past the Due Date.

Remaining Past Due

Number

An Invoice that is overdue.

Reporting Currency

Text

The ISO 3 letter currency code.

Sent To Contact

Text

Status

Text

{'AUTHORISED', 'DRAFT', 'FORECAST', 'PAID', 'SUBMITTED'}

Tax Amount

Number

The Tax amount of the Invoice in the currency it was transacted in.

Tax Amount (RC)

Number

The Tax amount of the Invoice in the reporting currency.

Tax Type

Text

From Xero.

e.g. {'EXEMPTINPUT', 'INPUT2', 'NONE', 'OUTPUT2', 'RRINPUT'}

Tracking Category 0

Text

Tracking Category 1

Text

Tracking Option 0

Text

Tracking Option 1

Text

Transaction Currency

Text

The ISO 3 letter code of the Invoice transaction currency.

Unit Amount

Number

The Unit Line Amount in the transaction currency.

Did this answer your question?