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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lnz
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

@v-shex-msft., thanks!

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

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @lnz,

 

Can you please share some sample data for test?

How to Get Your Question Answered Quickly

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft., thanks!

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.