Power Pivot and Closing Price

Question

I am trying to use power pivot to analyze a stock portfolio at any point in time.

The data model is:

transactions table with buy and sell transactions
historical_prices table with the closing price of each stock
security_lookup table with the symbol and other information about the stock (whether it’s a mutual fund, industry, large cap, etc.).

One to many relationships link the symbol column in security_lookup to the transactions and historical_prices tables.

I am able to get the cost basis to work correctly by doing sumx(transactions, quantity*price). However, I’m not able to get the current value of my holdings. I have a measure called “Current Price” which finds the most recent closing price by

Current Price :=
CALCULATE (
    LASTNONBLANK ( Historical_prices[close], min[close] ),
    FILTER (
        Historical_Prices,
        Historical_prices[date] = LASTDATE ( historical_prices[date] )
    )
)

However, when I try to find the current value of a security by using

Current Value = sumx(transactions,transactions[quantity]*[Current Price])

the total is not accurate. I'd appreciate suggestions on a way to find the current value of a position. Preferably using sumx or an iterator function so that the subtotals are accurate.

Please only ask one question per post. You can create additional posts for additional questions. — Alexis Olson, Sep 05 '18 at 16:17

score 0 · Answer 1 · answered Sep 05 '18 at 16:26

The problem with your Current Value measure is that you are evaluating [Current Price] within the row context of the transactions table (since SUMX is an iterator), so it's only seeing the date associated with that row instead of the last date. Or more precisely, that row's date is the last date in the measure's filter context.

The simplest solution is probably to calculate the Current Price outside of the iterator using a variable and then pass that constant in so you don't have to worry about row and filter contexts.

Current Value =
    VAR CurrentPrice = [Current Price]
    RETURN SUMX(transactions, transactions[quantity] * CurrentPrice)

Power Pivot and Closing Price

1 Answers1