cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EnviroJack
Frequent Visitor

Connecting Invoices and credit notes tables from Xero

Hello all

 

I'm building a model using Xero data. The objective is to be able to show sales income by sales person or product over time.

 

Currently I can link sales person to Invoice by Invoice number and show over time using the invoice date., the invoices are broken down by product and line item prices.

 

The issue is connecting credit notes. This table is very similar to the invoice table. All credit notes should be linked to an invoice by reference e.g. INV-1234 relates to CN-1234 (99% of the time).

 

Credit notes will either be for the full amount of the invoice or partial amount and like invoice are broken down by product line item. Sometimes these credit notes will be issued after the invoice but this is not so relevant.

 

I was hoping someone has some experience of this and can suggest the best way to link the data? Should this be by combining the tables into a single fact table or as a calculated measure?

I've included some examples of the data below.

Thanks

Jack

 

 

Example Invoice table (some columns removed)

 

IdCreditNoteIdCreditNoteNumberReferenceTypeContact_ContactIdDateStatusLineAmountTypesLineItem_DescriptionLineItem_QuantityLineItem_UnitAmountLineItem_ItemCodeLineItem_AccountCodeLineItem_TaxAmountLineItem_LineAmountSubTotalTotalTaxTotalUpdatedDateUTCCurrencyCodeFullyPaidOnDateCurrencyRateRemainingCredit
1|923582a1-7e45-4665-84bc-dd34cf541ba9923582a1-7e45-4665-84bc-dd34cf541ba9CN-EB24262payflow: EB24262-RACCRECCREDIT3a01f9a4-5be6-48a0-b1da-4f3947ed56ad23/07/2018PAIDInclusiveOLD PIONEER Starter Fasteners - 100226.99DA-LC-B200953.9844.98953.9823/07/2018 17:49GBP23/07/201810
1|926b65ac-6697-415d-b9dc-b6c445ea21b7926b65ac-6697-415d-b9dc-b6c445ea21b7CN-EB11734payflow: EB11734-RACCRECCREDIT0bf49ab1-a5d3-4155-85a4-12dd2ea6696608/11/2017PAIDInclusiveSurcharge1105null20017.510587.517.510508/11/2017 16:06GBP08/11/201710
1|95bebdf6-9fad-423e-b64d-a49fb13fb5f495bebdf6-9fad-423e-b64d-a49fb13fb5f4CN-EB25349payflow: EB25349-RACCRECCREDIT883e92da-e3af-4eaa-82bc-0db38596154f22/08/2018PAIDInclusiveGranite - 4m227.36D-H-G-4.22009.1254.72192.4138.49230.922/08/2018 17:06GBP22/08/201810
1|95c41b28-a523-4fbf-b547-1595f0418f0395c41b28-a523-4fbf-b547-1595f0418f03CN-EB24240payflow: EB24240-RACCRECCREDIT6d3dd855-0c83-4b33-8f13-6c38b8dd962923/07/2018PAIDInclusivePanel Caps | Granite - 1.8m126.95F-FC-G20013.9283.41006.35201.461207.8123/07/2018 10:34GBP23/07/201810

 

Example Credit Note Table (some columns removed)

 

IdCreditNoteIdCreditNoteNumberReferenceTypeContact_ContactIdDateStatusLineAmountTypesLineItem_DescriptionLineItem_QuantityLineItem_UnitAmountLineItem_ItemCodeLineItem_AccountCodeLineItem_TaxAmountLineItem_LineAmountSubTotalTotalTaxTotalUpdatedDateUTCCurrencyCodeFullyPaidOnDateCurrencyRateRemainingCredit
1|923582a1-7e45-4665-84bc-dd34cf541ba9923582a1-7e45-4665-84bc-dd34cf541ba9CN-EB24262payflow: EB24262-RACCRECCREDIT3a01f9a4-5be6-48a0-b1da-4f3947ed56ad23/07/2018PAIDInclusiveOLD PIONEER Starter Fasteners - 100226.99DA-LC-B200953.9844.98953.9823/07/2018 17:49GBP23/07/201810
1|926b65ac-6697-415d-b9dc-b6c445ea21b7926b65ac-6697-415d-b9dc-b6c445ea21b7CN-EB11734payflow: EB11734-RACCRECCREDIT0bf49ab1-a5d3-4155-85a4-12dd2ea6696608/11/2017PAIDInclusiveSurcharge1105null20017.510587.517.510508/11/2017 16:06GBP08/11/201710
1|95bebdf6-9fad-423e-b64d-a49fb13fb5f495bebdf6-9fad-423e-b64d-a49fb13fb5f4CN-EB25349payflow: EB25349-RACCRECCREDIT883e92da-e3af-4eaa-82bc-0db38596154f22/08/2018PAIDInclusiveGranite - 4m227.36D-H-G-4.22009.1254.72192.4138.49230.922/08/2018 17:06GBP22/08/201810
1|95c41b28-a523-4fbf-b547-1595f0418f0395c41b28-a523-4fbf-b547-1595f0418f03CN-EB24240payflow: EB24240-RACCRECCREDIT6d3dd855-0c83-4b33-8f13-6c38b8dd962923/07/2018PAIDInclusivePanel Caps | Granite - 1.8m126.95F-FC-G20013.9283.41006.35201.461207.8123/07/2018 10:34GBP23/07/201810

 

 

 

 

 

 

3 REPLIES 3
karaoan
Kudo Commander
Kudo Commander

@EnviroJack you might want to try out: Xero - Reporting Consolidation Planning For Xero Power BI | Acterys this solutions give you an optimal data warehouse, Power BI model and reports with all Xero data. Also included are useful views that combine data that is typically viewed merged as opposed to raw API tables like invoices and credit notes. 

v-shex-msft
Community Support
Community Support

Hi @EnviroJack ,

How about add calculated columns to these tables to extract similar numeric part from these fields? Then you can simply use these number fields as key field to create relationship.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello @v-shex-msft ,

 

Could you please elaborate a little bit more about the procedure to get net sales from two tables? I don't quite understand well your approach.

 

For instance, in my case the data are as follows, showing one day and highlighting one customer:

 

Sales tables: 1) SalesHeaders, 2) SalesDetailsSales tables: 1) SalesHeaders, 2) SalesDetailsCredit tables: 1) CreditHeaders, 2) CreditDetailsCredit tables: 1) CreditHeaders, 2) CreditDetails

I used a method in the past (referred to in this post) but I'm pretty sure there is a more efficient way to do it.

 

Thank you and best regards,

 

Fernando

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!