Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mdparmar
New Member

i want to adjust invoices and receipts of customers in chronological order

I have two tables one is of invoices of various customers and the second table of receipts of multiple customers. I want to adjust invoices against invoice in chronological order.

 

The invoice table of multiple customers is as follows

CustomerCustomer NameInvoice DateInvoice DocDoc Type Amount 
112999ABC26/09/20228232104556RV22892
112999ABC07/10/20228232104988RV27955
112999ABC20/10/20228232105436RV35123
112999ABC22/10/20228232105542RV16695
112999ABC31/10/20228232105661RV100170
112999ABC31/10/20228232105700RV11210
112999ABC04/11/20228232105793RV11682
      
104743XYZ20/09/20228232104353RV95390
104743XYZ20/09/20228232104354RV13381
104743XYZ29/09/20228232104827RV47553
104743XYZ30/09/20228232104831RV2381
104743XYZ10/10/20228232105078RV63107
104743XYZ14/10/20228232105267RV15777
104743XYZ19/10/20228232105417RV3216
104743XYZ04/11/20228232105801RV7144
104743XYZ26/11/20228232106325RV3302
104743XYZ26/11/20228232106326RV4321
104743XYZ07/12/20228232106510RV22832
104743XYZ27/12/20228232107001RV4842
104743XYZ27/12/20228232107002RV25674
104743XYZ11/01/20238220318106RV16057
104743XYZ31/01/20238220318497RV2421
104743XYZ31/01/20238220318498RV23235
104743XYZ10/02/20238220318653RV8508

 

The receipts table of multiple customers is as follows;

 

CustomerCustomer NameReceipt DateReceipt DocDoc Type Amount 
112999ABC29/10/20221400021259DZ86971
112999ABC31/10/20221600020219DG6031
112999ABC13/01/20231400000657DZ80000
      
104743XYZ19/09/20221400017107DZ133185
104743XYZ28/09/20228280407881YC781
104743XYZ29/09/20221400018132DZ200000
104743XYZ30/09/20221600017640DG2403
104743XYZ27/10/20228280408177YC1475

 

I want to prepare the new table and desired output table will be as follows;

 

CustomerNew CodeCustomer NameInvoice DateInvoice DocDoc Type Amount Receipt DateReceipt DocDoc TypeAmount
11299910000368ABC26/09/20228232104556RV2289229/10/20221400021259DZ22892.00
11299910000368ABC07/10/20228232104988RV2795529/10/20221400021259DZ27955.00
11299910000368ABC20/10/20228232105436RV3512329/10/20221400021259DZ35123.00
11299910000368ABC22/10/20228232105542RV100129/10/20221400021259DZ1001.10
11299910000368ABC22/10/20228232105542RV603131/10/20221600020219DG6031.00
11299910000368ABC22/10/20228232105542RV966313/01/20231400000657DZ9662.90
11299910000368ABC31/10/20228232105661RV7033713/01/20231400000657DZ70337.10
11299910000368ABC31/10/20228232105661RV29833    
11299910000368ABC31/10/20228232105700RV11210    
11299910000368ABC04/11/20228232105793RV11682    
10474310007038XYZ20/09/20228232104353RV95390    
           
10474310007038XYZ20/09/20228232104353RV9539019/09/20221400017107DZ95390.00
10474310007038XYZ20/09/20228232104354RV1338119/09/20221400017107DZ13381.00
10474310007038XYZ29/09/20228232104827RV2441419/09/20221400017107DZ24413.99
10474310007038XYZ29/09/20228232104827RV78128/09/20228280407881YC781.00
10474310007038XYZ29/09/20228232104827RV2235829/09/20221400018132DZ22358.01
10474310007038XYZ30/09/20228232104831RV238129/09/20221400018132DZ2381.00
10474310007038XYZ10/10/20228232105078RV6310729/09/20221400018132DZ63107.00
10474310007038XYZ14/10/20228232105267RV1577729/09/20221400018132DZ15777.00
10474310007038XYZ19/10/20228232105417RV321629/09/20221400018132DZ3216.00
10474310007038XYZ04/11/20228232105801RV714429/09/20221400018132DZ7144.00
10474310007038XYZ26/11/20228232106325RV330229/09/20221400018132DZ3302.00
10474310007038XYZ26/11/20228232106326RV432129/09/20221400018132DZ4321.00
10474310007038XYZ07/12/20228232106510RV2283229/09/20221400018132DZ22832.00
10474310007038XYZ27/12/20228232107001RV484229/09/20221400018132DZ4842.00
10474310007038XYZ27/12/20228232107002RV2567429/09/20221400018132DZ25674.00
10474310007038XYZ11/01/20238220318106RV1605729/09/20221400018132DZ16057.00
10474310007038XYZ31/01/20238220318497RV242129/09/20221400018132DZ2421.00
10474310007038XYZ31/01/20238220318498RV656829/09/20221400018132DZ6567.99
10474310007038XYZ31/01/20238220318498RV240330/09/20221600017640DG2403.00
10474310007038XYZ31/01/20238220318498RV147527/10/20228280408177YC1475.00
10474310007038XYZ31/01/20238220318498RV12789    
10474310007038XYZ10/02/20238220318653RV8508    
1 REPLY 1
lbendlin
Super User
Super User

Your sample data does not match your description. Are you assuming that receipt values match invoice values?  Is there an indication on the receipt which invoice it is for?

 

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors