Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

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;
ID          Year

1            2019

1            2019

1            2020

1            2020


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-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. 

Super User
Super User

@HB13 , if you need a difference between the same year you need a common date table. The expected output is not very clear with your example

Helpful resources


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.