Portfolio Slicer - Calculated columns
Table Dates
The Dates table has this list of calculated columns to help filter date periods:
| Attribute | Formula | Description |
| Mth-Day | =Format([Date],"mm-dd") | Month day in format MM-DD (shorter version, used to reduce space used for axis labels in some reports) |
| QuotesAvailable | =IF((COUNTROWS(RELATEDTABLE(Quotes))=0 || [ExcludeFlag]=1) ,"No","Yes") | Specifies if there are any(!) quotes available for this date (value "Yes") or no (value "No"). |
| QuotesRowNo | =IF(Dates[QuotesAvailable] <> "Yes", Blank(), CALCULATE(COUNTROWS(Dates), All(Dates), Dates[Date] <= Earlier(Dates[Date]) , Dates[QuotesAvailable] = "Yes")) | Sequential number of dates that includes just dates with quotes |
| Days - Current | =IF(Dates[Date] = MAX(Quotes[Date]),"Yes","No") | Value is "Yes" just for date of the last available quote. That is value is "Yes" just for one last date with quote. |
| Days - Last 10 Days | =IF([QuotesAvailable]="Yes" && [QuotesRowNo] >CALCULATE(MAX([QuotesRowNo]), ALL(Dates))-10 && [QuotesRowNo] <= CALCULATE(MAX([QuotesRowNo]), All(Dates)), "Yes","No") | Value is "Yes" just for last 10 dates with quotes. |
| Days - Last 20 Days | =IF([QuotesAvailable]="Yes" && [QuotesRowNo] >CALCULATE(MAX([QuotesRowNo]), ALL(Dates))-20 && [QuotesRowNo] <= CALCULATE(MAX([QuotesRowNo]), All(Dates)), "Yes","No") | Value is "Yes" just for last 20 dates with quotes. |
| Days - Last 30 Days | =IF([QuotesAvailable]="Yes" && [QuotesRowNo] >CALCULATE(MAX([QuotesRowNo]), ALL(Dates))-30 && [QuotesRowNo] <= CALCULATE(MAX([QuotesRowNo]), All(Dates)), "Yes","No") | Value is "Yes" just for last 30 dates with quotes. |
| Days - Last 50 Days | =IF([QuotesAvailable]="Yes" && [QuotesRowNo] >CALCULATE(MAX([QuotesRowNo]), ALL(Dates))-50 && [QuotesRowNo] <= CALCULATE(MAX([QuotesRowNo]), All(Dates)), "Yes","No") | Value is "Yes" just for last 50 dates with quotes. |
| Days - Last 100 Days | =IF([QuotesAvailable]="Yes" && [QuotesRowNo] >CALCULATE(MAX([QuotesRowNo]), ALL(Dates))-100 && [QuotesRowNo] <= CALCULATE(MAX([QuotesRowNo]), All(Dates)), "Yes","No") | Value is "Yes" just for last 100 dates with quotes. |
| Days - Last 200 Days | =IF([QuotesAvailable]="Yes" && [QuotesRowNo] >CALCULATE(MAX([QuotesRowNo]), ALL(Dates))-200 && [QuotesRowNo] <= CALCULATE(MAX([QuotesRowNo]), All(Dates)), "Yes","No") | Value is "Yes" just for last 200 dates with quotes. |
| Days - Last 365 Days | =IF([Date] <= MAX(Quotes[Date]) && [Date] >= MAX(Quotes[Date])- (365*1) && [QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" just for last 365 calendar days. |
| Years - Current | =IF(Year(MAX(Quotes[Date])) = Year(Dates[Date]) && Dates[QuotesAvailable] = "Yes", "Yes","No") | Value is "Yes" just for current year (based on last available quote date) |
| Years - Last 2 | =IF(Year(Dates[Date]) >= Year(MAX(Quotes[Date]))-1 && [QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" just for current year (based on last available quote date) and one previous year. |
| Years - Last 3 | =IF(Year(Dates[Date]) >= Year(MAX(Quotes[Date]))-2 && [QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" just for current year (based on last available quote date) and 2 previous year. |
| Years - Last 4 | =IF(Year(Dates[Date]) >= Year(MAX(Quotes[Date]))-3 && [QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" just for current year (based on last available quote date) and 3 previous year. |
| Years - Last 5 | =IF(Year(Dates[Date]) >= Year(MAX(Quotes[Date]))-4 && [QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" just for current year (based on last available quote date) and 4 previous year. |
| Years - Last 6 | =IF(Year(Dates[Date]) >= Year(MAX(Quotes[Date]))-5 && [QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" just for current year (based on last available quote date) and 5 previous year. |
| Mths - Current | =IF(Dates[Date] >= (MAX(Quotes[Date]) - Day(MAX(Quotes[Date])) + 1) && Dates[QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" for dates in current month (based on last available quote) |
| Mths - Last 2 | =IF(DateAdd(Dates[Date],1,MONTH) >= (MAX(Quotes[Date]) - Day(MAX(Quotes[Date])) + 1) && Dates[QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" for dates in current month (based on last available quote) and dates in 1 previous month. |
| Mths - Last 3 | =IF(DateAdd(Dates[Date],2,MONTH) >= (MAX(Quotes[Date]) - Day(MAX(Quotes[Date])) + 1) && Dates[QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" for dates in current month (based on last available quote) and dates in 2 previous month. |
| Mths - Last 4 | =IF(DateAdd(Dates[Date],3,MONTH) >= (MAX(Quotes[Date]) - Day(MAX(Quotes[Date])) + 1) && Dates[QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" for dates in current month (based on last available quote) and dates in 3 previous month. |
| Mths - Last 5 | =IF(DateAdd(Dates[Date],4,MONTH) >= (MAX(Quotes[Date]) - Day(MAX(Quotes[Date])) + 1) && Dates[QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" for dates in current month (based on last available quote) and dates in 4 previous month. |
| Mths - Last 6 | =IF(DateAdd(Dates[Date],5,MONTH) >= (MAX(Quotes[Date]) - Day(MAX(Quotes[Date])) + 1) && Dates[QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" for dates in current month (based on last available quote) and dates in 5 previous month. |
| Mths - Last 12 | =IF((DateAdd(Dates[Date],11,MONTH) >= (MAX(Quotes[Date]) - Day(MAX(Quotes[Date])) + 1) || DateAdd(Dates[Date],11,MONTH) = Blank()) && Dates[QuotesAvailable]="Yes", "Yes","No") | Value is "Yes" for dates in current month (based on last available quote) and dates in 11 previous month. |
Table Trans
The Trans table has the following set of calculated columns:
| Attribute | Formula | Description |
| TransQty | =IF(RELATED(TransType[IgnoreQtyFlag]) = 1 , Blank(), ROUND(Trans[TransQtyStmt]*RELATED(TransType[QtySign]),3)) | |
| TransCashValue | =ROUND((Trans[TransPrice]* Related(TransType[CashTransSign]) *IF(RELATED(TransType[IgnoreQtyFlag]) = 1, 1, Trans[TransQtyStmt]) - Trans[TransFee] ) * IF(ISBLANK(Trans[ExchRate]),1,Trans[ExchRate]), 2) | |
| TransBookValue | = IF(RELATED(TransType[BookValueSign]) = 0, Blank() , IF(RELATED(TransType[QtySign]) = -1 , Trans[TransQtyStmt] * CALCULATE(SUMX(Trans, [TransPrice] * Trans[TransQty] + Trans[TransFee]) / SUM(Trans[TransQty]) , ALLEXCEPT(Trans, Trans[Portfolio], Trans[Symbol]) , Trans[TransDate] < EARLIER(Trans[TransDate]) ) , Trans[TransPrice] * IF(RELATED(TransType[IgnoreQtyFlag])=1,1,Trans[TransQtyStmt]) + Trans[TransFee] ) * RELATED(TransType[BookValueSign]) ) |