Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate portfolio value per asset

I have next assets portfolio transactions table:

DATEASSET-IDASSET-NAMEAMOUNTBALANCE
21.04.20162FB300300
01.07.20161AAPL500500
10.02.20171AAPL100600
15.05.20171AAPL250850

 

 Also I have:

  • Calendar table (universal DateDim)
  • Stock table (stock prices table with DATE, ASSET-ID, PRICE columns). So I have one PRICE column for all assets.
  • [Stoсk Price CUR] measure that calculate actual stock price without data gaps

 

I need a measure that calculate my portfolio total value for each date (without date gaps).


I'm trying to modeling a next algorytm

  1. Calculate actual balance for each asset [measure]
    Here I use LASTNONBLANK formula

    Balance WoG = 
    VAR d =
        SELECTEDVALUE ( 'Calendar'[Date] )
    RETURN
        CALCULATE (
            LASTNONBLANK(split[end-balance];1);
            split[Date] <= d;
            CROSSFILTER ( 'Calendar'[Date]; split[date]; NONE )
        )

2. Calculate actual value for each asset [measure]

Here I'm trying to use that formula:

 

 

Asset Value = 
    CALCULATE([Balance WoG]*[Stock Price CUR])

 

That works fine only then I use asset-id as Legend on my chart (see screenshot), but I inderstand, that I need something more savvy

3. Sum all assets value [measure]
Can't understand how to solve it

 

Dev Report Screenshot

 

  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous., thanks!

    The problem was in the data model and my lack of understanding of some DAX functions
    So the topic is closed.

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Anonymous., thanks!

      The problem was in the data model and my lack of understanding of some DAX functions
      So the topic is closed.