Measure or Formating to calculate the difference between 2 columns on 2 tables but share same codes

[Marcus-G]

Hey! Ive been wracking my head on this and trying to find some help. So I have a 2 reports that pull directly out of our accounting program and the 2 tables are for sales invoices and sales credits. 

I am trying to find a way to create a single table that has it calculated but it always results in some strange numbers that dont match.

The tables do share the same items codes and want to calculate the total difference when selecting different filters for reporting purposes. 

The Outcome is I want a table that looks like below but im not sure what im missing to match it. 

 

[Anonymous · Accepted solution]

Hi @Marcus-G ,

As per your description, I have created two tables.

You can refer to the following steps.
1. create relationship for both tables.

2.Create measure and calculate the sum separately.

sumCredit =
CALCULATE(SUM(SalesCredits[Amount]),FILTER(ALL(SalesCredits),'SalesCredits'[ItemCode] = MAX('SalesCredits'[ItemCode])))

sumInvoice =
CALCULATE(SUM(SalesInvoices[Amount]),FILTER(ALL(SalesInvoices),'SalesInvoices'[ItemCode] = MAX('SalesInvoices'[ItemCode])))

3. Create the measure and calculate the difference.

diff =
[sumInvoice] - [sumCredit]

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[PhilipTreacy]

@Marcus-G 

Please provide your source data (or at least  a representative sample of it) - I can't give you a solution without the source data to work with.

Please provide a file or a table pasted in here, not an image.

Regards

Phil