Odtnhj

I would like to calculate average yield between two relation tables of a given date

Table1 Table2
+-------------------------------+ +-------------------------------+
| ID TradeDate Amount | | ID TradeDate Yield |
+-------------------------------+ +-------------------------------+
| 1 2018/11/30 100 | | 1 2018/11/8 2.2% |
| 1 2018/11/8 101 | | 1 2018/8/8 2.1% |
| 1 2018/10/31 102 | | 1 2018/5/8 2.0% |
| 1 2018/9/30 103 | | 2 2018/9/8 1.7% |
| 2 2018/11/30 200 | | 2 2018/6/8 1.6% |
| 2 2018/10/31 203 | | 2 2018/3/8 1.5% |
| 2 2018/9/30 205 | | 3 2018/10/20 1.7% |
| 3 2018/11/30 300 | | 3 2018/7/20 1.6% |
| 3 2018/10/31 300 | | 3 2018/4/20 1.6% |
| 3 2018/9/30 300 | +-------------------------------+
+-------------------------------+

I create a table named 'DateList' and use slicer to select a specified date.

Screen Shot DateList.

I want to achieve the following result:

as of *11/9/2018*
+-----------------------------------------------------------------+
| ID LastDate Value LatestYieldDate LastYield |
+-----------------------------------------------------------------+
| 1 2018/11/8 101 2018/11/8 2.2% |
| 2 2018/10/31 203 2018/9/8 1.7% |
| 3 2018/10/31 300 2018/10/20 1.7% |
+-----------------------------------------------------------------+
| Total 604 1.7836% | 
+-----------------------------------------------------------------+

Currently, I use the following formula to achieve the partial result

Create 2 measures in table1

 LastDate = 
 VAR SlicerDate = MIN(DateList[Date])
 VAR MinDiff =
 MINX(FILTER(ALL(Table1),Table1[ID] IN VALUES(Table1[ID])),
 ABS(SlicerDate - Table1[TradeDate]))
 RETURN
 MINX(FILTER(ALL(Table1),Table1[ID] IN VALUES(Table1[ID])
 && ABS(SlicerDate - Table1[TradeDate]) = MinDiff),
 Table1[TradeDate])

 Value = CALCULATE(SUM(Table1[Amount]), FILTER(Table1, Table1[TradeDate] = [LastDate]))

Create 2 measures in table2

LastYieldDate = 
VAR SlicerDate = MIN(DateList[Date])
VAR MinDiff =
 MINX(FILTER(ALL(Table2),Table2[ID] IN VALUES(Table2[ID])),
 ABS(SlicerDate - Table2[TradeDate]))
RETURN
 MINX(FILTER(ALL(Table2),Table2[ID] IN VALUES(Table2[ID])
 && ABS(SlicerDate - Table2[TradeDate]) = MinDiff),
 Table2[TradeDate])

LastYield = CALCULATE(SUM(Table2[Yield]), FILTER(Table2, 
 Table2[TradeDate] = [LastYieldDate]))

I have no idea to calculate right average yield between 2 tables
Here is my current result.

Screen Shot Current Result.

You'll first need to create a bridge table for the ID values so you can work with both tables more easily.

IDList = VALUES(Table1[ID])

Now we'll use IDList[ID] on our visual instead of the ID from one of the other tables.

The measure we use for the average last yield is a basic sum-product average:

LastYieldAvg = 
DIVIDE(
 SUMX(IDList, [Value] * [LastYield]),
 SUMX(IDList, [Value])
)

Note that when there is only a single ID value, it simplifies to

[Value] * [LastYield] / [Value] = [LastYield]