Forum Discussion
Nested Filter Referencing Two Unrelated Tables.
- 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)
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.
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)