Nested Filter Referencing Two Unrelated Tables.

[Caz_16]

Hello everyone,

I am feeling absolutely beat. I have been trying to get a conditional value to populate based on if two conditions are met, which will then be used in future formulas, but apparently I am at a complete lack of understanding as to how PBI operates when it comes to evaluating expressions. 

I have 3 tables with Stock Data pulling form a SQL server:

Stock_main - Columns (Stock ID (Primary), Ticker, Company_name, stock_index)

Owned - Columns (Purchase ID, Stock ID (Foreign), Buy_Date, Shares, Cost, Portfolio)
Price - Columns (Stock ID (Foreign), Date_stamp, Open, High, Low, Close, Volume)

What I want is to determine if I have owned a specific stock on that date. I have been trying methods with IF statements and FILTER statements to iterate through each row of the "Price" table to look at the stock ID, match it with a Stock ID from the "Owned" table, then determine if the Date_stamp in the price table is greater than (after) the date i bought it by referencing the "Buy_Date" column in the "Owned" table. 

This is one that that works at a hit rate of about 80% of the time. 

Owned at Date Conditional =

IF(

AND (FIRSTDATE('price'[Date_stamp])>=

FIRSTDATE(

FILTER(

FILTERS('owned'[buy_date]),

FIRSTNONBLANK('owned'[stock_id],"")=FIRSTNONBLANK('price'[stock_id],"")

)

),

FIRSTNONBLANK('price'[stock_id],"") IN DISTINCT('owned'[stock_id])

),

TRUE(),

FALSE())

Like I said, the above formula results in a TRUE or FALSE correctly about 80% of the time. 

Please help. There has to be an easier way to do this but I'm just so wrapped up in this incorrect method that I am stuck running in circles. 

Cheers,

[JarroVGIT]

Tip: start using variables to make your code more readable. It took me a while to read your code. Now my biggest question is; is this a calculated column and if so, to what table? You say "I want to determine if I owned a stock on a date", where does that date come from? What has that to do with table Price? (Or does that table only contains rows for OLHC when you owned a stock?) 

Please explain a bit more what you are trying to achieve (and where) and wat the expected result looks like 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Caz_16]

Djerro,

Sorry for the unclear question, allow me to clarify. 

This will be a calculated column, or measure (Im unsure which to be using), within the Price table. The price table holds the the HLOC and volume for each day, for each stock. 

Date_Stamp	Stock_ID	Open	High	Low	Close	Volume
1/1/2020	1234	$5	$5.50	$4.90	$5.12	1M
1/1/2020	5678	$100	$101	$98	$100	5M
1/1/2020	789	$40	$45	$40	$45	4M
1/2/2020	1234	$5.12	$5.60	$4.98	$5.45	2M
1/2/2020	5678	$100	$105	$98	$102	6M
1/2/2020	789	$45	$45	$38	$39	10M

What I need is a column (either this table, or a new table) where a TRUE or FALSE is populated if I owned that stock on that date. See below, I did not own stock 789 on 1/1 or 1/2. The reason I need this is because I will use this to determine if I will include that stock's value, multiplied by the number of shares I own, in my overall portfolio. What I want is for it to look at the "Date_stamp" column and determine if that date is after the "Buy_date" column, which comes from a different table. 

Date_Stamp	Stock_ID	Owned at Date
1/1/2020	1234	TRUE
1/1/2020	5678	TRUE
1/1/2020	789	FALSE
1/2/2020	1234	TRUE
1/2/2020	5678	TRUE
1/2/2020	789	FALSE

Also I found some tips yesterday where people are recommending using a seperate calendar table in order to perform date/time analysis in PBI. 

[JarroVGIT · Accepted solution]

You never sell your stock? (Can we assume that if a row has TRUE for one date and stock, it will remain true for the rest of the table for that stock?)
If so, the calculated column (big difference with a measure) is like this (typing on my phone so sorry for mistakes):
VAR curDate= Price[date_stamp]
VAR curStock = Price[Stock_ID]
RETURN
IF ( COUNTROWS( FILTER( ALL(Owned), Owned[Stock_ID]= curStock && Owned[BuyDate] < curDate)) > 0, TRUE, FALSE)