Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors