Forum Discussion

HB13's avatar
HB13
Helper I
4 years ago

How to calculate difference between two fields from tables with a many to many relationship

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 
Table1[Year] - Table2[Year_] so the outputs for.... 
2019 - 2017

2019 - 2018

2020-2017

2020-2018  etc

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

All assistance will be greatly appreciated.