How to create a Pivot table with 2 many-to-many relationship tables

[Xceller]

I have two tables, [ActualSales] and [ActualPayment].

They are both have 3 columns,
[ActualSales] contains: "Date", "InvoiceNo", "SalesAmount"
[ActualPayment] contains: "Date", InvoiceNo", PaymentAmount"

I tried building a linked Date table and a linked Invoice table and hoping to connect and two tables.
[lnkDate]
[lnkInvoiceNo]

I dropped the Date field from the [LinkedDate] table to the Row Field of the pivot table and the InvoiceNo from [LinkedInvoice] table to the pivot table row field. Also dropped the ActualSales from [ActualSales] table and PaymentAmount from the [ActualPayment] table to the Value field of the pivot table.

Although the Grand Totals of the pivot table are correct, the invoice by dates are incorrect - repeating the same amounts.

Is my dataset un-pivotable? is there a workaround?

This is the result I am hoping to see:

InvoiceDate------------InvoiceNo------------ActualSalesAmount-----------------PaymentDate-----------PaymentAmount
05/30/2018-----------10001-----------------$1,000.00----------------------------6/30/2018--------------$1,000.00

any help or comments will be appreciated.

 

[VBA Geek]

Looks like the dimension table you created is not filtering the two fact tables. Did you link them with relationships? are the invoice numbers on both tables related?