Retrieve a value from an unrelated table based on date

[Anonymous]

Hello, I would appreciate help with correctiong my DAX formula for calculating the value of a stock portfolio over time. The relationship structure is shown below (Link to file: https://1drv.ms/x/s!Amfa-bu5w625jtVeXvbfd0IhKn5_Pw?e=dbnPk0) 

My data  set includes three tables,

• a stockhistory table (qryHist), populated with three fields showing daily stock price by ticker symbol (ticker, date, close price)
• a list of sample purchases (qryPurch), identifying the symbol, the purchase date, and the quantity
• a date table

The date table (qryDate) is on the 'one' side of a one-to-many relationship with the history table (qryHist), linked by date, The date table (qryDate) is on the 'one' side of a one-to-many relationship with the purchase table (qryPurch), linked by date.

My intent is to create a measure which can be used to graph the change in the portfolio over time, either by ticker symbol, or for the entire portfolio. This seems straightforward but my formula routinely returns no results or the wrong resuts.

I have tried the following DAX formula:

PortfolioValueByDate:=VAR SelectedDate = MAX(qryDate[Date])
RETURN
	SUMX(
		FILTER(
			qryPurch,
			qryPurch[BuyDate] <= SelectedDate
		),
		qryPurch[Quan] *
		LOOKUPVALUE(
				qryHist[Close],
				qryHist[Date], SelectedDate,
				qryHist[Ticker], qryPurch[Ticker]
		)
	)

I have also tried to create the measure using Calculate and Filter:

CalcPortfolioValueByDate:=VAR SelectedDate = MAX(qryDate[Date])
RETURN
	SUMX(
		FILTER(
			qryPurch,
			qryPurch[BuyDate] <= SelectedDate
		),
		qryPurch[Quan] *
		CALCULATE(Values(qryHist[Close]),
			        FILTER(qryHist,
			        	      qryHist[Date] = SelectedDate &&
			        	      qryHist[Ticker] = qryPurch[Ticker]
			        )
		)
	)

..and multiple permutations of this theme. Ideally, the measure should produce the sum of the (quantity of shares ) * ( respective share price(s) on any date). Retrieving the total quantity by date works well, however, any attempt to use the history table (qryHist) to pull in the stock price on a given date causes the formula to fail.  Any assistance will be appreciated. Thank you.

Relationship Structure

[johnt75]

Not quite sure which date you want to use to get the closing price. In my code I was using the BuyDate from the purchase table to find the value of the stock when it was purchased. Because you are iterating over the purchase table you can use any date from there to get the appropriate closing price.

[Anonymous]

Hi, the date for the closing price has to come from the history table (qryHist) because that is the only source for the daily change in price. The purchase table is static in that it only shows the price on the date of purchase. Example, if I bought AT&T shares at $13 on Tues, they may be $14 dollars next Fri. The portfolio value would go up by a dollar, but my original purchase price would remain the same. My goal is to track the change in value of the portfolio over time based on the price of the security (which changes daily). That is why the closing price is necessary, and the daily closing price is only tracked in the history table (qryHist).  Thanks and I hope I didn't confuse the issue.

[johnt75 · Accepted solution]

It looks like your original code should work for that. Are you sure that you have a closing price for every date?

[Anonymous]

You are absolutely briilliant! My sample data for building the tool included days that weren't in the history table.  Yes, I feel like an idiot (three days of fooling around with formulas). I truly appreciate the generosity of your time. Best wishes. /P

[Anonymous]

Hi johnt75, thank you sincerely for taking time to respond.  I appreciate the assistance. The updated measure formula still returns incorrect information (see image below). Regardless of which measure formula above is used, I get the same result. Also, I'm not sure the change would accomplish the intent because the lookup would only return the price on the purchase date (which would remain unchanged), and not reflect the price on other dates the securities were traded (which is needed to track the change in portfolio value over time).

Thanks in advance for your (or any other) feedback.  /pww 

[johnt75]

Try

PortfolioValueByDate :=
VAR SelectedDate =
    MAX ( qryDate[Date] )
RETURN
    SUMX (
        FILTER ( qryPurch, qryPurch[BuyDate] <= SelectedDate ),
        qryPurch[Quan]
            * LOOKUPVALUE (
                qryHist[Close],
                qryHist[Date], qryPurch[BuyDate],
                qryHist[Ticker], qryPurch[Ticker]
            )
    )