Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
@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.
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
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |