Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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.
@MKV Maybe:
Quick_Measure_Difference =
var _TABLE1 = MAX(CURRENT[BALANCE_AMT])
var _TABLE2 = MAX(HISTORY[BALANCE_AMT])
return
_TABLE1 - _TABLE2
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 😞
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.
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.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
43 | |
26 | |
21 |