Our datawarehouse recieve a daily file with overview of all stock positions (about 23 000 rows daily), which are transformed into a Slowly changing dimension table that contains all the history of the balance of each fund ('Positions' table) and the daily price for each fund ('Prices' table).
What I need is to display a line or bar chart of the whole portfolio on any given time by multiplying Position[ShareCount] (The number of shares for each fund on each account) with Prices[NAV] (the price for each share), for any given date.
There are two main challanges (and many more it seems):
(1) To multiply the columns together, as I do not have (and cannot have) an active relationship between the Positons table and the Price table. This would create a ambigious "many-to-many" relationship as both tables contains historical data.
The Positions table also just show the date when the position was activated, but I want to find the value on the selected date based on the price that are in the Prices table.
(2) Sum the total value of total portolio based on the most recent change on any given position that are before the selected date.
E.g., I want to see the value of 5 different positions. One of them have not changed in sharecount in a year, while the rest have changed in a varying degree (montly/weekly/quartly ect.). Whenever I try to SUM the total sharevalue over time it only sums based on the position date, and not the actual value of the position on any given time in the future.
As of now I have tried a bunch of stuff, and many tricks found on this forum, without luck. Here is my current code, but that will probably be of no use and help. I have been trying to implement many suggested solutions here at this forum, with no luck.
--VAR selecteddate = SELECTEDVALUE(Dato[Date])
--VAR Maxbalancedate = CALCULATE(MAX(Positions[PosDate]), ALL(Dato[Date]), Positions[PosDate]<= selecteddate)
--VAR Value = CALCULATE(SUMX(Positions,Positions[ShareCount]*RELATED(Price[NAV])),(USERELATIONSHIP(Positions[ISINNo],Prices[ISINNo])))
--VAR valgt_dato = MAX(Dato[Date])
--VAR siste_beholdningsdato = (Positions[PosDate]) <= valgt_dato
--VAR Valgt_siste_beholdning = CALCULATE(MAX(Positions[ShareCount]), siste_beholdningsdato)
--VAR siste_beholdningsdato = max(Positions[PosDate]) <= valgt_dato
--VAR Beholdning = CALCULATE(SUM(FILTER(Positions, siste_beholdningsdato)))
-- CALCULATE(SUM(Positions[ShareCount]),siste_beholdningsdato <= valgt_dato)
-- __cDate >= Positions[Fradato] ||
-- __cDate <= Positions[Tildato]
The desired result is to be able to see the total value of the portfolio drilled down at any given date.