I work on financial assets portfolio analysis project
At current stage I have next tables:
[Calendar] - universal singe calendar with [date], [year], [month] e.t.c fields
[Assets]
- [asset-id]
- [asset-type] - STOCK, CASH, DEPOSIT, e.t.c.
- [account-id]
[Accounts]
- [account-id]
- [currency]
- [balance-on-create]
[Split] - table to store transactions entries
- [split-id]
- [date]
- [account-id]
- [asset-id]
- [transaction-id]
- [start-balance]
- [end-balance] = [start-balance] + [amount]
- [amount]
- [currency] USD, EUR, e.t.c
[Stocks] - stocks prices
- [stock-id]
- [asset-id]
- [date]
- [stock-price]
- [currency] - USD, EUR, e.t.c.
Three purchases of Apple shares for cash are stored as follows:
id date account-id asset-id start-balance end-blance amount currency
3 01.07.2016 2 7 400 000 302 500 -97 500 USD
4 01.07.2016 4 1 0 500 500 USD
15 01.07.2016 2 7 302 500 289299 -13 212 USD
16 01.07.2016 4 1 500 600 100 USD
17 01.07.2016 2 7 289 288 250 363 -38925 USD
18 01.07.2016 4 1 600 850 250 USD
I looking for a measure that calulates price of portfolio on date
So I think I need to create [current-price] measure at [Split] table
I'm try to calculate it through [current-stock-price] at [Stocks] table
current-stock-price =
var d = SELECTEDVALUE('calendar'[Date])
RETURN
CALCULATE (
MIN( stocks[price]);
ALLEXCEPT(stocks;'stocks'[asset-id]);
'stocks'[date] = d
)
But it did not bring results.
What measures I need and how I should reconfig my data model to calculate portfolio price on date?
