Portfolio Slicer - Calculated Measures
The majority of calculations in Portfolio Slicer are implemented as calculated measures using the DAX language in PowerPivot. These measures can be used later in building reports.
Table Date
The Date table has just one calculated measure to calculate the day for which the latest quote is available.
| Measure | DAX Formula | Description |
| LatestDate | =MAX(Quotes[Date]) | Last date with quote |
Table Report
The Report table is a dummy table that was created to group generic calculated measures that will be used in reports or in other calculations.
| Measure | DAX Formula | Description |
| Book Price | =IF(Report[Qty]=0, Blank(), Report[Book Value] / Report[Qty]) | Average book price of symbol |
| Book Value | =CALCULATE( SUMX(Trans, Trans[TransBookValue]) , DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])) , All(Dates) ) | Book value of symbol at specified period end |
| Cash Value | =IF(COUNTROWS(FILTER(Symbol, Symbol[Symbol] = "* Cash"))<1,Blank(), CALCULATE(SUM(Trans[TransCashValue]) , DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])) , All(Dates) , All(Symbol) ) ) | Cash value in portfolio for the end of selected period |
| Cash Value Start | =IF(DateAdd(FIRSTDATE(Dates[Date]),-1, Day) = Blank(), Blank() , CALCULATE(CALCULATE(Report[Cash Value], DateAdd(FIRSTDATE(Dates[Date]),-1, Day)) , AllExcept(Dates, Dates[Year], Dates[Month], Dates[Date], Dates[MonthInYear], Dates[Quarter], Dates[QuorterInYear]) ) ) | Cash value in portfolio for the start of selected period |
| Deposits | =CALCULATE(SUMX(FILTER(Trans, Trans[Symbol]="* Cash"), Trans[TransCashValue]), TransType[DepositFlag]=1) | Deposits for selected period |
| Dividends | =CALCULATE(SUMX(Trans, [TransPrice]), TransType[DividendFlag] = 1) | Dividends paid for selected period |
| Equity Value | =SUMX(Symbol, Report[Qty] * Report[Price]) | Equity value at the end of selected period |
| Fees | =SUM(Trans[TransFee]) | Transaction fees paid during selected period |
| Gain | =Report[Equity Value] - Report[Book Value] | Capital gain/loss (Equity value at the end of selected period minus book value) |
| Gain and Dividends | =Report[Gain] + Report[Dividends] | Capital gains/loses plus dividends/distributions paid for selected period |
| Growth | =Report[Total Value] - Report[Start Value] - Report[Dividends] - Report[Deposits] | Value growth for selected period (Total value minus start value minus dividends minus deposits) |
| Price | =IF(Report[Price NoDefault]=0, Report[Book Price], Report[Price NoDefault]) | Price of security at the end of selected period. If quote is not find, then use book price |
| Price NoDefault | =CALCULATE(VALUES(Quotes[Close]) , LASTNONBLANK( DATESBETWEEN(Dates[Date], Blank(), LastDate(Dates[Date])) , CALCULATE(COUNT(Quotes[Close]) )) ) | Price of security at the end of selected period |
| Price NoDefault Adj | =CALCULATE(VALUES(Quotes[Adj Close]) , LASTNONBLANK( DATESBETWEEN(Dates[Date], Blank(), LastDate(Dates[Date])) , CALCULATE(COUNT(Quotes[Adj Close]) )) ) | Adjusted price of security at the end of selected period |
| Qty | =CALCULATE(ROUND(SUM(Trans[TransQty]),3) , DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])) , All(Dates) ) | Quantity of security at the end of selected period |
| RoR Portfolio | =XIRR[RoR-P] | Rate of Return for Portfolio (Annualized). Uses logic similar to Excel XIRR function |
| Ror Symbol | =XIRR[RoR-S] | Symbol Rate of Return (annualized). Uses logic similar to Excel XIRR function |
| Start Value | =IF(DateAdd(FIRSTDATE(Dates[Date]),-1, Day) = Blank(), Blank() , CALCULATE( CALCULATE(Report[Total Value] , ALLEXCEPT(Dates, Dates[Date], Dates[Month], Dates[Year]) ) , DateAdd(FIRSTDATE(Dates[Date]),-1, Day) ) ) | Equities and cash value at the start of selected period |
| Total Value | =Report[Cash Value] + Report[Equity Value] | Equities and cash value at the end of selected period |
| Value-Deposits | =Report[Total Value]-Report[Deposits] | Equity and cash value at the end of selected period minus Deposits during that period |
ReportHold
The ReportHold table is a dummy table that is used to group calculated measures related to current investment holdings. The majority of these measures are based on generic "Report" measures, but they also check if at the end of a selected period the specific symbol was held in the portfolio. This extra checking is desired so that when symbol and related measures are dropped in the PivotTable, only the symbols and calculations in the portfolio are shown.
| Book Price Hold | =IF(ReportHold[Qty Hold]=0, Blank(), Report[Book Price]) | Book price of symbol. Visible just if symbol quantity at the end of period is not 0. |
| Book Value Hold | =CALCULATE(Report[Book Value], FILTER(VALUES(Symbol[Symbol]), Report[Qty] <> 0)) | Book value of symbol. Visible just if symbol quantity at the end of period is not 0. |
| Cash Value Calc | =CALCULATE(SUM(Trans[TransCashValue]) , DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])) , All(Dates) , All(Symbol) ) | Cash value at the end of period. Used in other calculations. |
| Cash Value Hold | =IF(COUNTROWS(FILTER(Symbol, Symbol[Symbol] = "* Cash"))>=1, ReportHold[Cash Value Calc], Blank()) | Portfolio cash value. Visible just for symbol "* Cash" and at portfolio level or above. |
| Daily Chg | =SUMX(Dates, SUMX(FILTER(Symbol,Report[Qty]<>0), Report[Qty] * CALCULATE(SUM(Quotes[DayChg])))) | Daily change sum for holdings in portfolio.Visible just if symbol quantity at the end of period is not 0. |
| Dividends To Date | =SUMX(Portfolio, CALCULATE(SUMX(Trans, [TransPrice]) , DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])) , TransType[DividendFlag] = 1, FILTER(VALUES(Symbol[Symbol]),Report[Qty]<>0) , All(Dates) ) ) | Total dividends paid to date. Visible just if symbol quantity at the end of period is not 0. |
| Equity Value Hold | =SUMX(Symbol, IF(Report[Qty]=0, Blank(), Report[Equity Value])) | Total equity value at the end of period. Visible just if symbol quantity at the end of period is not 0. |
| First Date | =CALCULATE(MINA(Trans[TransDate]) , FILTER(VALUES(Symbol[Symbol]),Report[Qty]<>0)) | First date of symbol transaction. Visible just if symbol quantity at the end of period is not 0. |
| Gain No Dividend | =ReportHold[Equity Value Hold]-ReportHold[Book Value Hold] | Symbols capital gain. |
| Gain With Dividend | =ReportHold[Equity Value Hold] - ReportHold[Book Value Hold] + ReportHold[Dividends To Date] | Symbols capital gain plus dividends paid to date. |
| Last Days Chg | =SUMX(FILTER(VALUES(Symbol[Symbol]), Report[Qty] <> 0) , Report[Qty] * CALCULATE(VALUES(Quotes[DayChg]), All(Dates[Date]), Dates[Days - Current] = "Yes") ) | Equity value change for last trading date. Visible just if symbol quantity at the end of period is not 0. |
| Price Chg Perc | =IF(ReportHold[Equity Value Hold] = 0, Blank(), ReportHold[Equity Value Hold]/ReportHold[Book Value Hold]-1) | Equity value change in percents since first purchase. Visible just if symbol quantity at the end of period is not 0. |
| Price Hold | =IF(NOT HASONEVALUE(Symbol[Symbol]) || Report[Qty]=0, Blank(), Report[Price]) | Symbol price at the end of period. Visible just if symbol quantity at the end of period is not 0. |
| Qty Hold | =IF(Not HASONEVALUE(Symbol[Symbol]), Blank(), IF(ROUND(Report[Qty],3) = 0, Blank(), Report[Qty])) | Symbol quantity at the end of period. Visible just if symbol quantity at the end of period is not 0. |
| Rate of Return Hold | =IF(Report[Qty]=0, Blank() , XIRR[RoR-S]) | Rate of Return for symbols (calculation is similar to Excel function XIRR). Visible just if symbol quantity at the end of period is not 0. |
| Total Value Hold | =ReportHold[Cash Value Hold] + ReportHold[Equity Value Hold] | Equity and cash value at the end of period. Visible just if symbol quantity at the end of period is not 0. |
Table ReportSales
The ReportSales table is a "dummy" table used to group calculated measures related to the sales type of report. These measures return values only when the specific symbol was sold in selected period.
| First Buy Date | =IF(NOT HASONEVALUE(Symbol[Symbol]), Blank() , CALCULATE(MINA(Trans[TransDate]) , DATESBETWEEN(Dates[Date], Blank(), LastDate(Dates[Date])) , All(Dates) , TransType[QtySign]=1, TransType[CashTransSign] <> 0 , FILTER(Symbol, ReportSales[Qty Sold] <> 0) ) ) | Symbol first buy date. Visible only for symbols that were sold in selected period. |
| Last Sales Date | =IF(NOT HASONEVALUE(Symbol[Symbol]), Blank() , CALCULATE(MAXA(Trans[TransDate]) , DATESBETWEEN(Dates[Date], FirstDate(Dates[Date]), LastDate(Dates[Date])) , All(Dates) , TransType[QtySign]=-1, TransType[CashTransSign] <> 0 ) ) | Symbol last sales date. Visible only for symbols that were sold in selected period. |
| Qty Bought | =IF(NOT HASONEVALUE(Symbol[Symbol]), Blank() , CALCULATE(SUM(Trans[TransQtyStmt]) , DATESBETWEEN(Dates[Date], FirstDate(Dates[Date]), LastDate(Dates[Date])) , All(Dates) , TransType[QtySign]=1, FILTER(Symbol, ReportSales[Qty Sold] <> 0) ) ) | Symbols quantity bought in selected period. Visible only for symbols that were sold in selected period. |
| Qty End | =IF(ReportSales[Qty Sold] = 0, Blank(),CALCULATE(Report[Qty], LastDate(Dates[Date]))) | Symbols quantity at the end of the selected period. Visible only for symbols that were sold in selected period. |
| Qty Sold | =IF(NOT HASONEVALUE(Symbol[Symbol]), Blank() , CALCULATE(SUM(Trans[TransQtyStmt]) , DATESBETWEEN(Dates[Date], FirstDate(Dates[Date]), LastDate(Dates[Date])) , All(Dates) , TransType[QtySign]=-1, TransType[CashTransSign] <> 0 ) ) | Number of shares sold in selected period. Visible only for symbols that were sold in selected period. |
| Qty Start | =IF(NOT HASONEVALUE(Symbol[Symbol]), Blank() , CALCULATE(Report[Qty], FirstDate(Dates[Date]), FILTER(Symbol, ReportSales[Qty Sold] <> 0)) ) | Symbols quantity of shares at the start of selected period. Visible only for symbols that were sold in selected period. |
| RoR Sales | =CALCULATE(XIRR[RoR-S], All(Dates), FILTER(Symbol, ReportSales[Qty Sold]<>0)) | Rate of return (annualized) for symbols sold in selected period. Formula is similar to Excels function XIRR. |
| Sales Amnt | =CALCULATE(SUM(Trans[TransCashValue]) + SUM(Trans[TransFee]) , DATESBETWEEN(Dates[Date], FirstDate(Dates[Date]), LastDate(Dates[Date])) , All(Dates) , TransType[QtySign]=-1, TransType[CashTransSign] <> 0 ) | Symbols total sales amount. Does not include deduction for sales fee (per Canadian tax requirements) |
| Sales Book Value | =CALCULATE(ABS(SUM(Trans[TransBookValue])) , DATESBETWEEN(Dates[Date], FirstDate(Dates[Date]), LastDate(Dates[Date])) , All(Dates) , TransType[QtySign]=-1 ) | Symbol Book value. Visible only for symbols that were sold in selected period. |
| Sales Fees | =IF(NOT HASONEVALUE(Symbol[Symbol]), Blank() , CALCULATE(SUM(Trans[TransFee]) , DATESBETWEEN(Dates[Date], FirstDate(Dates[Date]), LastDate(Dates[Date])) , All(Dates) , TransType[QtySign]=-1, TransType[CashTransSign] <> 0 ) ) | Symbol sales fees. Visible only for symbols that were sold in selected period. |
| Sales Profit | =ReportSales[Sales Amnt] - ABS(ReportSales[Sales Book Value]) - ReportSales[Sales Fees] | Symbol sales profit/loss. Visible only for symbols that were sold in selected period. |
Table ReportSymbol
The ReportSymbol table is a dummy table that is used to group calculated measures related to the symbol, not holdings.
| Symbol Price | =IF(HASONEVALUE(Symbol[Symbol]), Average(Quotes[Adj Close]), Blank()) | Adjusted symbol price at the end of selected period |
| Symbol Price 200Days Avg | =IF(HASONEVALUE(Symbol[Symbol]), Average(Quotes[200DayAvg]), Blank()) | Symbol 200 quote days price average |
| Symbol Price 50Days Avg | =IF(HASONEVALUE(Symbol[Symbol]), Average(Quotes[50DayAvg]), Blank()) | Symbol 50 quote days price average |
Table SymbolCountry
The SymbolCountry table has one calculated measure that splits the Report[Total Value] calculated measure based on the symbol percentage in each country/region. This is used in a chart to show how your portfolio holdings are allocated between countries/regions.
| Total Value by Country | =SUMX(SymbolCountry, SUMX(FILTER(Symbol,Symbol[Symbol]=SymbolCountry[Symbol]), SymbolCountry[Percent] * Report[Total Value])) | Total value split by specified country/region percentage |
Table SymbolSector
The SymbolSector table has one calculated measure that splits the Report[Tatal Value] calculated measure based on the symbol's percentage in each sector. This is used in charts to show how your portfolio holdings are allocated between different sectors.
| Total Value by Sector | =SUMX(SymbolSector, SUMX(FILTER(Symbol,Symbol[Symbol]=SymbolSector[Symbol]), SymbolSector[Percent] * Report[Total Value])) | Total value split by specified symbol sector percentage |
Table XIRR
The XIRR tabe is an experimental feature of Portfolio Slicer that is currently only partially implemented. This table is used to calculate the symbol's and portfolio's annualized return rate based on similar logic to the XIRR Excel function:
Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with a guess), XIRR cycles through the calculation until the result is accurate to within 0.000001 percent. If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned. The rate is changed until:
where:
di = the ith, or last, payment date.
d1 = the 0th payment date.
Pi = the ith, or last, payment.
Because PowerPivot does not support recursion, a "brute force" method is used to calculate rate of return. A XIRR table is created with different XIRR rate values and the value for each of XIRR record is calculated. Then XIRR value that produces the formula value closest to zero is used as the Rate of Return. This approach requires many calculations and is slow, but it returns results and the performance penalty is accepted. When we create the sum for the above formula, it is split into 3 pieces:
- Start - sometimes the start is the first day of the selected period and sometimes it is the first transaction date. Because of that we calculate the start value separately.
- End - sometimes the end is the last day of the period and sometimes is the day when the symbol was sold. Because of that we calculate the end value separately.
- Base - this is the sum calculation for all transactions after the first day but before the last day.
Separate Rate of Return formulas are used for symbol and portfolio, as in these cases we treat "in" and "out" transactions differently.
The XIRR table is hidden from the frontend as we create calculated measures in other tables based on measures calculated through the XIRR table.
The flollowing is a listing for the calculated measures of the XIRR table.
| XIRRFirstDate | =IF(DateAdd(FIRSTDATE(Dates[Date]),-1,day) = Blank() || CALCULATE(Report[Qty], DateAdd(FIRSTDATE(Dates[Date]),-1,day)) = 0 , XIRR[XIRRFirstTransDate], XIRR[XIRRFirstPeriodDate] ) | First date in selected period when there was transaction or if symbol quantity was already not 0, then first day of period |
| XIRRLastDate | =IF(LASTDATE(Dates[Date]) >= CALCULATE(MAX(Quotes[Date]), All(Quotes)) , CALCULATE(MAX(Quotes[Date]), All(Quotes)) , LASTDATE(Dates[Date]) ) | Minimum of two: Last day of Period or Last day with quote |
| XIRRSumEnd-S | =Report[Equity Value] / ((1+AVERAGE(XIRR[XIRR])) ^ ((Int(XIRR[XIRRLastDate] - XIRR[XIRRFirstDate])/365))) | End part of XIRR formula for symbol (internal) |
| XIRRSumBase-S | =IF(COUNTRows(Trans)=0, Blank() , IF(DateAdd(FIRSTDATE(Dates[Date]),-1,day) = Blank() || CALCULATE(Report[Qty], DateAdd(FIRSTDATE(Dates[Date]),-1,day)) = 0 , SUMX(Trans, Trans[TransCashValue] / ((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[TransDate] - XIRR[XIRRFirstTransDate] )/365) ) ) , SUMX(Trans, Trans[TransCashValue] / ((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[TransDate] - CALCULATE(DateAdd(FIRSTDATE(Dates[Date]),-1,day), All(Trans), VALUES(Dates[Date])) )/365)) ) ) ) | Base part of XIRR formula for symbol (Internal) |
| XIRRSumStart-S | =IF(DateAdd(FIRSTDATE(Dates[Date]),-1,day)=Blank(), Blank() , (-1)* CALCULATE(Report[Equity Value], DateAdd(FIRSTDATE(Dates[Date]),-1,day)) ) | Start part of XIRR formula for symbol (internal) |
| RoR-S | =IF((COUNTROWS(Trans)=0 && Report[Qty] = 0) , Blank() , MINX(TOPN(1, ALL(XIRR) , ABS( XIRR[XIRRSumStart-S] + XIRR[XIRRSumBase-S] + XIRR[XIRRSumEnd-S] ) , 1 ) , XIRR[XIRR] ) ) | Rate of return for Stocks/Equities |
| XIRRFirstTransDate | =CALCULATE(MIN(Trans[TransDate]), AllExcept(Trans, Trans[Symbol], Trans[Portfolio]), VALUES(Symbol[Symbol]), VALUES(Portfolio[Portfolio])) | First transaction date |
| XIRRFirstPeriodDate | =CALCULATE(DateAdd(FIRSTDATE(Dates[Date]),-1,day), All(Trans), VALUES(Dates[Date])) | First date in selected period |
| XIRRSumStart-P | IF(DateAdd(FIRSTDATE(Dates[Date]),-1,day)=Blank(), Blank() , (-1)* CALCULATE(Report[Total Value], DateAdd(FIRSTDATE(Dates[Date]),-1,day)) ) | Starting part of XIRR formula for portfolio (Internal) |
| XIRRSumEnd-P | =Report[Total Value] / ((1+AVERAGE(XIRR[XIRR])) ^ ((Int(XIRR[XIRRLastDate] - XIRR[XIRRFirstDate])/365))) | Ending part of XIRR formula for portfolio (Internal) |
| XIRRSumBase-P | =IF(DateAdd(FIRSTDATE(Dates[Date]),-1,day) = Blank() || CALCULATE(Report[Total Value], DateAdd(FIRSTDATE(Dates[Date]),-1,day)) = 0 , SUMX(FILTER(Trans, Trans[Symbol] = "* Cash"), (-1)* Trans[TransCashValue] / ((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[TransDate] - XIRR[XIRRFirstTransDate] )/365) ) ) , SUMX(FILTER(Trans, Trans[Symbol] = "* Cash"), (-1) * Trans[TransCashValue] / ((1+AVERAGE(XIRR[XIRR])) ^ (Int(Trans[TransDate] - CALCULATE(DateAdd(FIRSTDATE(Dates[Date]),-1,day), All(Trans), VALUES(Dates[Date])) )/365)) ) ) | Base part of XIRR formula for portfolio (Internal) |
| RoR-P | =IF(COUNTROWS(Trans)=0 && Report[Total Value] = 0, Blank() , MINX(TOPN(1, ALL(XIRR) , ABS(XIRR[XIRRSumStart-P] + XIRR[XIRRSumBase-P] + XIRR[XIRRSumEnd-P] ) , 1 ) , XIRR[XIRR] ) ) | Rate of Return for Portfolio (when cash is included in calculation) |