Lookup value using time intelligence?

Used for anything related to the new Power BI Desktop and Service tools
gmerrifield
Posts: 1
Joined: Fri Apr 19, 2019 3:04 am

Lookup value using time intelligence?

Postby gmerrifield » Fri Apr 19, 2019 9:55 am

I am a novice Power BI user trying to do a complex query and would appreciate any help from this forum.
I have a "Table_1" that includes the following columns: 1)stock_symbol, 2)date, 3)price, 4)Target_1, and 5)Target_2 for many symbols and many dates.
I also have a "Table_2" that has historical stock prices (High, Low, Open, Close) for all of the symbols in Table_1. The columns in Table_2 are: 1)stock_symbol, 2)date, 3)High, 4)Low, 5)Open, and 6)Close.

What I want to do, for each row in Table_1, is lookup the date in Table_2 at which the stock price for that symbol hit each of the two targets (or not) without going back in time from the date specified in Table_1.

What DAX functions would work best to accomplish this?

Other Questions:
Do I need a custom Calendar Table in my data model? Something like this: https://exceleratorbi.com.au/build-reus ... wer-query/
Should I unpivot the columns in Table_2?
The relationship that probably needs to exist between the two tables is many-to-many. What's the best way to structure the relationships between these tables or do I need to create another table or more to work around many-to-many? and then what cross filter direction needs to be selected for those relationships?

Here is a sample of the data I have in my data model now:

Table_1
Stock_Symbol Date Price Target1 Target2 Target1_HIT Target2_HIT
OIH 6/25/2018 $25.40 $27.94 $22.86
QQQ 6/25/2018 $182.40 $200.64 $164.16
XLF 6/25/2018 $26.71 $29.38 $24.04
GLD 6/25/2018 $120.12 $132.13 $108.11
BAC 6/25/2018 $28.48 $31.33 $25.63
AAPL 6/25/2018 $182.80 $201.08 $164.52 8/1/2018 N/A {This is an example of what I want for each row}
INTC 6/25/2018 $50.71 $55.78 $45.64
T 6/25/2018 $31.78 $34.96 $28.60
T 6/25/2018 $31.78 $34.96 $28.60
QQQ 6/25/2018 $171.08 $188.19 $153.97
QQQ 6/25/2018 $171.23 $188.35 $154.11
EEM 6/25/2018 $43.50 $47.85 $39.15

Table_2

Stock_Symbol Date High Low Open Close
AAPL 6/21/2018 188.35 184.94 187.25 185.46
AAPL 6/22/2018 186.15 184.7 186.12 184.92
AAPL 6/25/2018 184.92 180.73 183.4 182.17
AAPL 6/26/2018 186.53 182.54 182.99 184.43
AAPL 6/27/2018 187.28 184.03 185.23 184.16
AAPL 6/28/2018 186.21 183.8 184.1 185.5
AAPL 6/29/2018 187.19 182.91 186.29 185.11
AAPL 7/2/2018 187.3 183.42 183.82 187.18
AAPL 7/3/2018 187.95 183.54 187.79 183.92
AAPL 7/5/2018 186.41 184.28 185.26 185.4
AAPL 7/6/2018 188.43 185.2 185.42 187.97
AAPL 7/9/2018 190.68 189.3 189.5 190.58
AAPL 7/10/2018 191.28 190.18 190.71 190.35
AAPL 7/11/2018 189.78 187.61 188.5 187.88
AAPL 7/12/2018 191.41 189.31 189.53 191.03
AAPL 7/13/2018 191.84 190.9 191.08 191.33
AAPL 7/16/2018 192.65 190.42 191.52 190.91
AAPL 7/17/2018 191.87 189.2 189.75 191.45
AAPL 7/18/2018 191.8 189.93 191.78 190.4
AAPL 7/19/2018 192.55 189.69 189.69 191.88
AAPL 7/20/2018 192.43 190.17 191.78 191.44
AAPL 7/23/2018 191.96 189.56 190.68 191.61
AAPL 7/24/2018 193.66 192.05 192.45 193
AAPL 7/25/2018 194.85 192.43 193.06 194.82
AAPL 7/26/2018 195.96 193.61 194.61 194.21
AAPL 7/27/2018 195.19 190.1 194.99 190.98
AAPL 7/30/2018 192.2 189.07 191.9 189.91
AAPL 7/31/2018 192.14 189.34 190.3 190.29
AAPL 8/1/2018 201.76 197.31 199.13 201.5
AAPL 8/2/2018 208.38 200.35 200.58 207.39
AAPL 8/3/2018 208.74 205.48 207.03 207.99

Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest