DAX query is not returning correct output

[MKV]

Hi All,

I have three tables - CURRENT having latest date data, HISTORY having all dates data including latest date and DATE having all the dates info. DATE table has two indicator columns CURRENT_IND and PREVIOUS_IND that can be used to filter latest date and previous latest date.

In my model, I have one to many relationship between CURRENT and HISTORY using ACCT_ID column. I have one to many relationship between HISTORY and DATE table using BUSINESS_DATE column.

In my report view, I have total 5 columns in the report view - CURRENT.BUSINESS_DATE, CURRENT.ACCT_ID, CURRENT.BALANCE, HISTORY.BUSINESS_DATE, HISTORY.BALANCE, DATE.PREVIOUS_IND. As I have put FILTER on DATE.PREVIOUS_IND = Y so I am getting data of only previous latest date data from HISTORY. So for each ACCT_ID, i am getting now current and previous balance in the report view.

I now want a measure which will give me difference between current balance and previous balance. How can I do it?

Quick_Measure_Difference =

var _TABLE1 = SELECTEDVALUE(CURRENT[BALANCE_AMT])

var _TABLE2 = SELECTEDVALUE(HISTORY[BALANCE_AMT])

return

CALCULATE(_TABLE1 - _TABLE2)

I have written below code however it is not returning correct data. If I have selected 5 ACCT_IDs, as soon as I select this measure, report starts showing 25 rows.

can someone please help.

[Greg_Deckler]

@MKV Maybe:

Quick_Measure_Difference =
var _TABLE1 = MAX(CURRENT[BALANCE_AMT])
var _TABLE2 = MAX(HISTORY[BALANCE_AMT])
return
_TABLE1 - _TABLE2

[MKV]

Sorry It is not working. 

Suppose I filter only on 5 ACCT_IDs and get data without this measure, report is showing just 5 rows. We are good till here. As soon as I am adding this measure in CURRENT table and drag/drop it in the report view, many rows are showing. Looks like it is bringing ALL the ACCT_IDs somehow from the CURRENT table 😞

[MattAllington]

Your data is loaded incorrectly.  Read my article here https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

never join 2 fact tables together

load all your transaction data into the one table. Have a current/history column if you like

join to a date table. 
Do not use the date column from your fact table in your visual. 

[MKV]

Sorry Matt, I have some specific requirement that's why I have to bring CURRENT and HISTORY both. FYI, CURRENT has 200 columns and HISTORY has only 20 columns.

I removed the date columns of CURRENT/HISTORY from visuals and it has reduced many bad rows but it is still doing some cartisan product and bringing all the rows of ACCT_ID from HISTORY with BALANCEs of different dates.