Subtract fields from two tables with a many to many relationship

[HB13]

Hi PBI Gang

I have two tables Table1 and Table2 that have a many to many relationship (they are joined on their ID, ID_ columns)... each of these tables have a field containing a Year value.
I need to calculate the difference between these two fields.

For example;
Table1
ID          Year

1            2019

1            2019

1            2020

1            2020

Table
ID_    Item      Year_

1         A         2017

1         B         2018

How do I create a measure that calculates the difference between the fields? It would need to be as
YearDiff = Table1[Year] - Table2[Year_] with the output being;
ID    Item   YearDiff
1        A            2
1        B            1
1        A            3

1        B            2
where each Year in Table2 is subtracted from each Year in Table1 where they match on their ID. I'm essentially trying to calculate the age of each item for every year its ID has been recorded. I hope that makes sense.

These fields are not picking up in a DAX measure. I cannot do a lookup from one Table2 into Table1 or vice versa because of the many to many... I am not sure how to to go about this

All assistance will be greatly appreciated. 

[v-chenwuz-msft · Accepted solution]

Hi @HB13 ,

Please try this measure:

Measure = SUMX(VALUES(Table1[YEAR]),[YEAR])-SUM('Table'[Year])

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[claymcooper]

Can you merge the tables on the ID column? Then set up a custom column to calculate the difference between the years in the query editor?