Finding difference between two Tables generated from one table using DAX

Question

I want to find the difference between the sum of all the common elements between 2 tables.

We have one large table (Table A)

You can observe that neither the id, nor the date can be used individually as a unique identifier.

Based on certain filters(one of it is date), we have to create 2 tables:

We then have to find the difference between the sum of all the common elements.

Table 1 and 2 may not be real tables and could be simply virtual tables (defined using VAR) declared in order to create the measure.

I have tried the following code:

Difference = VAR Table1= ADDCOLUMNS('TableA', "id", CALCULATE(VALUES('TableA'[Id]), ALL('Date'),ALL('TableA'), USERELATIONSHIP('Date'[As of Date], Previous_Date[Previous_Date]),USERELATIONSHIP('Date'[As of Date], 'TableA'[Date])), "Value", 'TableA'[Value] )

VAR Table2= ADDCOLUMNS('TableA', "id", CALCULATE(VALUES('TableA'[Id]),ALL('TableA'), USERELATIONSHIP('Date'[As of Date], 'TableA'[Date])),"Value", 'TableA'[Value] )

VAR abc = CALCULATE(SUMX(Table1,IF(VALUES('TableA'[Id]) IN Table1 && VALUES('TableA'[Id]) in Table2,Table1[Value])) )

VAR pqr = CALCULATE(SUMX(Table2,IF(VALUES('TableA'[Id]) IN Table1 && VALUES('TableA'[Id]) in Table2,Table2[Value])) )

RETURN abc-pqr

Siddharth Thanga Mariappan Siddharth Thanga Mariappan · Accepted Answer · 2019-07-03T10:58:55

Pre-requisite :- Change the Data Type of your id columns to Text in both the tables.

Then, Create this Calculated Column in Table1 :-

Common_NotCommon_Table1 = 
         Var out1 = LOOKUPVALUE(Table2[id],Table2[id],Table1[id])
         Var out2 = IF(out1 <> "", "Common","Not-Common")
return out2

Then Create this Calculated Column in Table2 Likewise :-

Common_NotCommon_Table2 = 
         Var out1 = LOOKUPVALUE(Table1[id],Table1[id],Table2[id])
         Var out2 = IF(out1 <> "", "Common","Not-Common")
return out2

Then Create the two Measures to find the Sum - this can be created in any of your tables.

Sum_Common_Table1 = CALCULATE(SUM(Table1[corresponding value]), FILTER(Table1, Table1[Common_NotCommon_Table1] = "Common")) 

Sum_Common_Table2 = CALCULATE(SUM(Table2[corresponding value]), FILTER(Table2, Table2[Common_NotCommon_Table2] = "Common"))

Then Create the Difference Measure in any one of the Table:-

Common_Diff = [Sum_Common_Table2] - [Sum_Common_Table1]

The Output as Expected looks like,

Kindly accept the answer if it helps and let me know, how it works for you.

Finding difference between two Tables generated from one table using DAX

1 Answers