Calculation if ID match

[Robin96]

Hey all,

i need to create a calculation that substract an amount from table1 from table2. (Amount from table1 - amount from table2). This should be based on if the ID's match. So if Reference ID from table1 match the ID from table2, then do the substraction, else nothing.

i have posted a screenshot of an example.

[Ashish_Mathur]

Hi,

In the last row of the desired output table, shouldn't the answer be 300?

[Robin96]

Hey, yes. The last row of Desired output should be 300. thanks for highlighting this.

[Ashish_Mathur]

Hi,

In table2, write these calculated column formulas

Table1 reference ID = calculate(max(Table1[reference ID]),filter(Table1,Table1[reference ID]=earlier(Table2[id])))

Amount = calculate(sum(Table1[amount]),filter(Table1,Table1[reference id]=earlier(Table1 reference ID)))

Hope this helps.

[ryan_mayu]

@Robin96 

create relationship between two tables and create two columns

Column = RELATED('Table 1'[reference id])+0

Column 2 = if(ISBLANK(sumx(FILTER('Table 1','Table 2'[id]='Table 1'[reference id]),'Table 1'[amount])),0,'Table 2'[amount]-sumx(FILTER('Table 1','Table 2'[id]='Table 1'[reference id]),'Table 1'[amount]))

pls see the attachment below

[Robin96]

Hey, Thanks for the response! There is no relation between these two ID's in the original semantic model. They both relate to the date table i have. Is there a way to add this link with for example TREATAS in the column syntax?

[ryan_mayu]

@Robin96 

then you change the column  to 

Column = maxx(FILTER('Table 1','Table 1'[id]='Table 2'[id]),'Table 1'[reference id])+0

[Robin96]

Hey,

i get this response on the syntax in the DAX, what am i doing wrong here?

[ryan_mayu]

your DAX is different from mine. maybe try to remove the part after "-"