Forum Discussion
Subtract columns from 2 different tables
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 !
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_2If 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_GaoIf 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
2 Replies
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_2If 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_GaoIf 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