Power BI - weighted average yield across 2 tables of a given date

Question

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.

score 1 · Accepted Answer · answered Nov 15 '18 at 16:23

1

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]

answered Nov 15 '18 at 16:23

Alexis Olson

38,724
7
42
64

Thanks again Alexis. Can i use DAX directly rather than to create a bridge table? – AndyChu Nov 16 '18 at 01:08
Hi Alexis, if i have additional column named [company] (such as A, B, C,...) in table 1 & table 2, how can i combine [company] & [ID] in bridge table [IDList]? Thanks – AndyChu Nov 16 '18 at 08:16
There are some [workarounds for creating relationships on multiple columns](https://stackoverflow.com/questions/46852514/how-to-join-tables-on-multiple-columns-in-power-bi-desktop). – Alexis Olson Nov 16 '18 at 15:58

Power BI - weighted average yield across 2 tables of a given date

1 Answers1

Linked