cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HB13
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;
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. 

1 REPLY 1
amitchandak
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

 

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

Helpful resources

Announcements
Carousel_PBI_Wave1

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.