Subtract columns from 2 different tables

[Anonymous]

Hello, I am trying to subtract values from 2 columns from 2 different tables. When I use SUM(column1)- SUM(column2), it is subtracting the values but it ignores the date value in the table and ends up subtracting everything. I want to remove Qty from table 1 that are in table 2 with date still considered. Please attached the data sample below:

Table 1
Item	Qty	Date
111	10	2/25/2022
112	21	2/25/2022
111	15	2/26/2022
112	23	2/26/2022

Table 2
Item	Qty	Date
111	5	2/25/2022
112	7	2/25/2022
111	3	2/26/2022
112	10	2/26/2022

Required output
Item	Qty	Date
111	5	2/25/2022
112	14	2/25/2022
111	12	2/26/2022
112	13	2/26/2022

I would really appreciate the help from the community!

Thank you in advance !

[Anonymous · Accepted solution]

Hi @Anonymous ，

Using a date table and a table with an item column and creating a relationship with the original table is a workable solution.

For example. First create two calculated tables.

Date = CALENDAR(MIN('Table 1'[Date]),MAX('Table 2'[Date]))

Table 3 = VALUES('Table 1'[Item])

Then create the following relationship.

If you want the calculated column.

Column = 
VAR _qty_1 = CALCULATE(SUM('Table 1'[Qty]))
var _qty_2 = CALCULATE(SUM('Table 2'[Qty]))
return
_qty_1-_qty_2

If what you want is a measure, you can use your original expression and drag it into the visual along with columns

'Date'[Date] and 'Table 3'[Item].

Attach the PBIX file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

That worked! Thank you so much. @Anonymous