Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate total stock portfolio value (with Slowly changing dimention tables)

Hi, 

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). 

cbruFSB_2-1666344622620.png


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. 

 

TotalPositionsDateRange = 
--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])))

--Return
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)))

--Return
--CALCULATE(MAX(Positions[ShareCount]), siste_beholdningsdato)
--CALCULATE(SUM(Positions[ShareCount]),siste_beholdningsdato)
-- CALCULATE(SUM(Positions[ShareCount]),siste_beholdningsdato <= valgt_dato)

--CALCULATE(
--    SUM(Positions[ShareCount]),
--    __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. 

2 REPLIES 2
daXtreme
Solution Sage
Solution Sage

Your model seems to be AMBIGUOUS (very dangerous trait). Please transform it into something that's free from ambiguity. I know the many-to-many above is not active but I assume that there's a measure, at least one, where you enable this relationship without disabling the other connections. Am I right? If you do so, your model calculates something.... but one can't be sure what. On top of that, I'd be extremely careful to use many-to-many. This is more ofter than not a sign the model has a problem. The many-to-many relationship has a very special meaning in Power BI and this should be well understood before it can be used with confidence. One should use this type only in case of granularity issues. This is the only one case where it makes sense to employ this type of connection between tables (apart from joining data from different islands in composite models but you don't have such a model).

Anonymous
Not applicable

Hi, 

Thank you for your quick reply. 
And yes, thanks for the advice on both Ambigousity and Granuality. 
The inactive relationship has just been used for testing purposes. 
The stated problem did exist also before the inactive many-to-many relationship were made. 

The main problem here is the granuality issues (as we have only a SCD table). 
We either want to create a DAX code, or model the dataset in Power Query to handle the SCD table in a persistent way. 
Do you have any advice on this? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors