Subtract values in rows based on categories in another table

[LunaD]

I have table 1

Date	ID	Value
23/02/2024	x234fs	21
23/02/2024	d4635d	2
23/02/2024	a324ss	54
23/02/2024	d35s22	3

table 2

Category	ID	Location
TypeA	x234fs	Lake A
TypeB	d4635d	Lake A
TypeA	a324ss	Garden
TypeB	d35s22	Garden

I want to subtract value of typeA to typeB in table 1 if IDs are in the same location and same month. Category typeA and typeB are fixed but there are many locations.
Expected new table like this:

Date	ID	Value
23/02/2024	x234fs	19
23/02/2024	d4635d	2
23/02/2024	a324ss	51
23/02/2024	d35s22	3

I have been trying to create new column in the table 1 like below but does not work. I appreciate all the help and suggestion. Thank you!!

``` 

SubtractionResult =
VAR LocationsWithAB =
FILTER(
VALUES(table_2),
CALCULATE(COUNTROWS(table_2), table_2[Category] = "Type A") > 0 &&
CALCULATE(COUNTROWS(table_2), table_2[Category] = "Type B") > 0
)
VAR Result =
SUMX(
LocationsWithAB,
VAR CurrentLocation = table_2[Location]
VAR AccountA = CALCULATE(VALUES(table_2[ID]), table_2[Location] = CurrentLocation, table_2[Category] = "Type A")
VAR AccountB = CALCULATE(VALUES(table_2[ID]), table_2[Category] = CurrentLocation, table_2[Category] = "Type B")
VAR ConsumptionA =
CALCULATE(
SUM(table_1[Value]),
table_1[ID] = AccountA &&
table_1[Date] IN VALUES(table_1[Date])

)
VAR ConsumptionB =
CALCULATE(
SUM(table_1[consumption]),
table_1[ID] = AccountB &&
table_1[Date] IN VALUES(table_1[Date])
)
RETURN
IF(
NOT(ISBLANK(ConsumptionA)) && NOT(ISBLANK(ConsumptionB)),
ConsumptionA - ConsumptionB,
ConsumptionB
)

)
RETURN
Result
```

[LunaD · Accepted solution]

Thanks for your suggestion @amitchandak !!
It's very close to what I need, just that it won't work if there are different dates in the table.
A little tweak fixed it:

Measure = if(max(Table2[Category]) ="TypeA", sum(Table1[Value])- CALCULATE(SUM(Table1[Value]),ALLEXCEPT(Table1, Table2[Location], Table1[Date]), Table2[Category]="TypeB"), SUM(Table1[Value]))

Cheers.

[amitchandak]

@LunaD , Hope two tables are joined. File is attached for reference 

Measure = if(max(Table2[Category]) ="TypeA", sum(Table1[Value])- CALCULATE(SUM(Table1[Value]),ALLEXCEPT(Table1, Table2[Location]), Table2[Category]="TypeB"), SUM(Table1[Value]))