Wrong Sum totals when using a SUM measure from two different Tables

[ozpeacenik]

I have a table of Expense totals by month and another table of individual Sales.

I have managed to link them using a key column of ProductYearMonth which is unique to the Expense table.

I can create a matrix with the Columns being Year Month and the rows being product and display Total Revenue and Expense by Product and Period, but I can't work how to calculate Surplus (Revenue-Expense) and ROI (Revenue divided by Expense).  I have tried creating a measure but nothing I try is acceptable as a DAX formula.

Thank you for any help you can give me.

[ozpeacenik · Accepted solution]

I have solved this problem.  I created a column for Revenue and then all my calcs worked.

Revenue =
CALCULATE (
SUM ( 'Gift Data'[Gift Amount] ),
FILTER (
ALL ( 'Gift Data'[Gift Date] ),
'Gift Data'[Gift Date] <= MAX ( 'Gift Data'[Gift Date] )
)
)