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)
| Id | CreditNoteId | CreditNoteNumber | Reference | Type | Contact_ContactId | Date | Status | LineAmountTypes | LineItem_Description | LineItem_Quantity | LineItem_UnitAmount | LineItem_ItemCode | LineItem_AccountCode | LineItem_TaxAmount | LineItem_LineAmount | SubTotal | TotalTax | Total | UpdatedDateUTC | CurrencyCode | FullyPaidOnDate | CurrencyRate | RemainingCredit |
| 1|923582a1-7e45-4665-84bc-dd34cf541ba9 | 923582a1-7e45-4665-84bc-dd34cf541ba9 | CN-EB24262 | payflow: EB24262-R | ACCRECCREDIT | 3a01f9a4-5be6-48a0-b1da-4f3947ed56ad | 23/07/2018 | PAID | Inclusive | OLD PIONEER Starter Fasteners - 100 | 2 | 26.99 | DA-LC-B | 200 | 9 | 53.98 | 44.98 | 9 | 53.98 | 23/07/2018 17:49 | GBP | 23/07/2018 | 1 | 0 |
| 1|926b65ac-6697-415d-b9dc-b6c445ea21b7 | 926b65ac-6697-415d-b9dc-b6c445ea21b7 | CN-EB11734 | payflow: EB11734-R | ACCRECCREDIT | 0bf49ab1-a5d3-4155-85a4-12dd2ea66966 | 08/11/2017 | PAID | Inclusive | Surcharge | 1 | 105 | null | 200 | 17.5 | 105 | 87.5 | 17.5 | 105 | 08/11/2017 16:06 | GBP | 08/11/2017 | 1 | 0 |
| 1|95bebdf6-9fad-423e-b64d-a49fb13fb5f4 | 95bebdf6-9fad-423e-b64d-a49fb13fb5f4 | CN-EB25349 | payflow: EB25349-R | ACCRECCREDIT | 883e92da-e3af-4eaa-82bc-0db38596154f | 22/08/2018 | PAID | Inclusive | Granite - 4m | 2 | 27.36 | D-H-G-4.2 | 200 | 9.12 | 54.72 | 192.41 | 38.49 | 230.9 | 22/08/2018 17:06 | GBP | 22/08/2018 | 1 | 0 |
| 1|95c41b28-a523-4fbf-b547-1595f0418f03 | 95c41b28-a523-4fbf-b547-1595f0418f03 | CN-EB24240 | payflow: EB24240-R | ACCRECCREDIT | 6d3dd855-0c83-4b33-8f13-6c38b8dd9629 | 23/07/2018 | PAID | Inclusive | Panel Caps | Granite - 1.8m | 12 | 6.95 | F-FC-G | 200 | 13.92 | 83.4 | 1006.35 | 201.46 | 1207.81 | 23/07/2018 10:34 | GBP | 23/07/2018 | 1 | 0 |
Example Credit Note Table (some columns removed)
| Id | CreditNoteId | CreditNoteNumber | Reference | Type | Contact_ContactId | Date | Status | LineAmountTypes | LineItem_Description | LineItem_Quantity | LineItem_UnitAmount | LineItem_ItemCode | LineItem_AccountCode | LineItem_TaxAmount | LineItem_LineAmount | SubTotal | TotalTax | Total | UpdatedDateUTC | CurrencyCode | FullyPaidOnDate | CurrencyRate | RemainingCredit |
| 1|923582a1-7e45-4665-84bc-dd34cf541ba9 | 923582a1-7e45-4665-84bc-dd34cf541ba9 | CN-EB24262 | payflow: EB24262-R | ACCRECCREDIT | 3a01f9a4-5be6-48a0-b1da-4f3947ed56ad | 23/07/2018 | PAID | Inclusive | OLD PIONEER Starter Fasteners - 100 | 2 | 26.99 | DA-LC-B | 200 | 9 | 53.98 | 44.98 | 9 | 53.98 | 23/07/2018 17:49 | GBP | 23/07/2018 | 1 | 0 |
| 1|926b65ac-6697-415d-b9dc-b6c445ea21b7 | 926b65ac-6697-415d-b9dc-b6c445ea21b7 | CN-EB11734 | payflow: EB11734-R | ACCRECCREDIT | 0bf49ab1-a5d3-4155-85a4-12dd2ea66966 | 08/11/2017 | PAID | Inclusive | Surcharge | 1 | 105 | null | 200 | 17.5 | 105 | 87.5 | 17.5 | 105 | 08/11/2017 16:06 | GBP | 08/11/2017 | 1 | 0 |
| 1|95bebdf6-9fad-423e-b64d-a49fb13fb5f4 | 95bebdf6-9fad-423e-b64d-a49fb13fb5f4 | CN-EB25349 | payflow: EB25349-R | ACCRECCREDIT | 883e92da-e3af-4eaa-82bc-0db38596154f | 22/08/2018 | PAID | Inclusive | Granite - 4m | 2 | 27.36 | D-H-G-4.2 | 200 | 9.12 | 54.72 | 192.41 | 38.49 | 230.9 | 22/08/2018 17:06 | GBP | 22/08/2018 | 1 | 0 |
| 1|95c41b28-a523-4fbf-b547-1595f0418f03 | 95c41b28-a523-4fbf-b547-1595f0418f03 | CN-EB24240 | payflow: EB24240-R | ACCRECCREDIT | 6d3dd855-0c83-4b33-8f13-6c38b8dd9629 | 23/07/2018 | PAID | Inclusive | Panel Caps | Granite - 1.8m | 12 | 6.95 | F-FC-G | 200 | 13.92 | 83.4 | 1006.35 | 201.46 | 1207.81 | 23/07/2018 10:34 | GBP | 23/07/2018 | 1 | 0 |
