Search code examples
powerbidaxpowerbi-desktopmeasure

Get a line chart of Portfolio Value over Time by using historic data from a different table

In the process of learning PowerBI, I'm building a small stock portfolio analyzing app.

PBIX - Here

I want to make a line chart of my Portfolio Valuation over Time.

I've stock prices in the table StockHistoricData1 on a day granularity. (If the data is missing, we should ignore that stock in calculating Portfolio Value) Here is a small sample -

StockQuoteTable StartDate EndDate Date series OPEN HIGH LOW PREV. CLOSE ltp close vwap 52W H 52W L VOLUME
VBL 19-01-2023 18-01-2024 18-01-2024 EQ 1244 1249.55 1196.4 1248.5 1229.75 1230.15 1227.71 1747 755 1854390
VBL 19-01-2023 18-01-2024 17-01-2024 EQ 1256 1263.6 1230.1 1266.1 1249.25 1248.5 1244.89 1747 755 2159125
VBL 19-01-2023 18-01-2024 16-01-2024 EQ 1273.8 1276 1256.05 1271.7 1266 1266.1 1267.45 1747 755 1320740
SBIN 19-01-2023 18-01-2024 18-01-2024 EQ 625.8 633.45 619.05 626 628.5 628.3 627.46 660.4 499.35 14695436
SBIN 19-01-2023 18-01-2024 17-01-2024 EQ 624 636.8 623 636.9 626.45 626 628.6 660.4 499.35 30016308
SBIN 19-01-2023 18-01-2024 16-01-2024 EQ 640 644.9 633.45 640.1 637.55 636.9 639.72 660.4 499.35 15025543
ADANIPORTS 19-01-2023 18-01-2024 18-01-2024 EQ 1171.05 1184.3 1143.45 1166.85 1155 1154.05 1159.02 1229.9 395.1 5918594
ADANIPORTS 19-01-2023 18-01-2024 17-01-2024 EQ 1180 1204.7 1159.45 1193.35 1169.9 1166.85 1180.1 1229.9 395.1 5268593
ADANIPORTS 19-01-2023 18-01-2024 16-01-2024 EQ 1203.6 1212 1186.8 1203.1 1194.5 1193.35 1198.1 1229.9 395.1 3173587

I've my Current Trades which includes the Stocks I own in table Tradebook_1 Here is a sample -

Symbol ISIN Trade Date Exchange Segment Series Trade Type Auction Quantity Price Trade ID Order ID Order Execution Time CurrentPrice
IREDA INE202E01016 17-01-2024 BSE EQ B buy FALSE 80 122.6 18654600 1.70547E+18 17-01-2024 13.27 122.7
POLYCAB INE455K01017 09-01-2024 BSE EQ A buy FALSE 2 4880 193925800 1.70479E+18 09-01-2024 15.25 4440
IREDA INE202E01016 28-12-2023 BSE EQ B sell FALSE 150 96.76 12881600 1.70374E+18 28-12-2023 11.00 122.7
IREDA INE202E01016 27-12-2023 NSE EQ EQ buy FALSE 50 99.75 27742304 1.1E+15 27-12-2023 15.16 122.7
POLYCAB INE455K01017 26-12-2023 NSE EQ EQ buy FALSE 2 5395.55 46925953 1.2E+15 26-12-2023 15.19 4440
POLYCAB INE455K01017 22-12-2023 BSE EQ A sell FALSE 3 5322.35 114766100 1.70323E+18 22-12-2023 13.44 4440

There are multiple buy and sell operations. Hence, Portfolio Value should increase and decrease accordingly. How does one go about creating a measure that queries price as per date in StockHistoricData1 and calculates corresponding Portfolio value taking into account Trades done in Tradebook

I tried using RELATED to get corresponding data by stock symbol but I failed when taking daily stock prices into account.

Solution

  • Try the following Measures:

    Cumlative Quantity = 
  var maxDate = MAX('DateTable'[Date])
  var cTbl = CALCULATETABLE('Tradebook_1', REMOVEFILTERS('DateTable'), 'Tradebook_1'[Trade Date] <= maxDate)
  var buys = CALCULATE(SUM('Tradebook_1'[Quantity]), cTbl, 'Tradebook_1'[Trade Type] = "buy")
  var sells = CALCULATE(SUM('Tradebook_1'[Quantity]), cTbl, 'Tradebook_1'[Trade Type] = "sell")
  return buys - sells

    Portfolio Value = 
  var maxDate = MAX('DateTable'[Date])
  return
    CALCULATE(
      SUMX(
        'StockHistoricData1',
        (
          var thisSymbol = [StockQuoteTable]
          var thisPrice = [close]
          var thisQty = [Cumlative Quantity]('Tradebook_1'[Symbol] = thisSymbol)
          return thisPrice * thisQty
        )
      ),
      'StockHistoricData1'[Date] = maxDate
    )

    Notes:

    • In Tradebook_1 you seem to be missing historic data. For example Symbol VPRPL has a net Qty of -160 (sold more than bought). So you will get negative portfolio value for this.