Qty Held | IF(HasOneValue(Symbol[Symbol]), var res = ROUND([Qty Held System], 6) var symb = VALUES(Symbol[Symbol]) RETURN IF(symb = "* Cash", 1, IF(res <> 0, res)))
|
Qty Sold | IF(HASONEVALUE(Symbol[Symbol]), CALCULATE(SUM(Transactions[QtyChange]), TransType[SellFlag]=1))*(-1)
|
Qty Start | IF(PreviousDay(Dates[Date]) <> Blank(), CALCULATE([Qty Held], PreviousDay(Dates[Date]), ALL(Dates)))
|
Qty Bought | IF(NOT HASONEVALUE(Symbol[Symbol]), Blank()
, CALCULATE(SUM(Transactions[QtyChange]), TransType[QtySign]=1 )
)
|
Symbol Price | IF(HASONEVALUE(Symbol[Symbol])
, IF(NOT(HASONEVALUE(ReportCurrency[ReportCurrency])), [Symbol Price Orig Currency]
, IF(VALUES(ReportCurrency[ReportCurrency]) = "*Original*" || VALUES(Symbol[Currency]) =VALUES(ReportCurrency[ReportCurrency]), [Symbol Price Orig Currency]
, var exchRate1 =
CALCULATE(
CALCULATE(AVERAGE(CurrencyConv[ExchRate])
, LASTNONBLANK(DatesBetween(Dates[Date], DateAdd(LastDate(Dates[Date]), -10, day), LastDate(Dates[Date]))
, CALCULATE(COUNT(CurrencyConv[ExchRate])
)
)
)
, CurrencyConv[CurrencyFrom] = VALUES(Symbol[Currency])
)
var exchRate = IF(ISBLANK(exchRate1), 1, exchRate1)
RETURN [Symbol Price Orig Currency] * exchRate
)
)
)
|
Equity Value | var RepCurr = IF(NOT(HASONEVALUE(ReportCurrency[ReportCurrency])), "*Original*", VALUES(ReportCurrency[ReportCurrency]))
RETURN
SUMX(VALUES(Symbol[Currency])
, CALCULATE(SUMX(Symbol , var qty = [Qty Held] RETURN IF(qty<>0 && Symbol[Symbol] <> "* Cash", qty * [Symbol Price Orig Currency]))
* IF(RepCurr = "*Original*" || repCurr = VALUES(Symbol[Currency]), 1,
var ExchRate =
CALCULATE(
CALCULATE(MAX(CurrencyConv[ExchRate])
, LASTNONBLANK(DatesBetween(Dates[Date], DateAdd(LastDate(Dates[Date]), -10, day), MAX(Dates[Date]))
, CALCULATE(COUNTROWS(CurrencyConv)
)
)
)
, CurrencyConv[CurrencyFrom] = VALUES(Symbol[Currency])
)
RETURN IF(ISBLANK(ExchRate), 1, ExchRate)
)
)
)
|
Qty Held System | CALCULATE(SUM(Transactions[QtyChange]), DATESBETWEEN(Dates[Date], Blank(), MAX(Dates[Date])), ALL(Dates), All(TransType), TransType[QtySign]<>0)
|
Price Last | CALCULATE(SUM(Quotes[Close]), LastDate(Dates[Date]))
|
Symbol Price Orig Currency | IF(NOT(ISEMPTY(CALCULATETABLE(Quotes, LastDate(Dates[Date]))))
, CALCULATE(SUM(Quotes[Close]), LastDate(Dates[Date]))
, CALCULATE(SUM(Quotes[Close])
, LASTNONBLANK(
DATESBETWEEN(Dates[Date], DateAdd(LastDate(Dates[Date]), -40, day), LastDate(Dates[Date]))
, CALCULATE(COUNT(Quotes[Symbol]) )
)
)
)
|
Cash Value | ROUND(IF(VALUES(Config[TrackCash])="Yes"
, IF(CONTAINS(Symbol,Symbol[Symbol],"* Cash")
, IF(NOT(HasOneValue(ReportCurrency[ReportCurrency])) || CALCULATE(IsEmpty(CurrencyConv), All(Dates))
, CALCULATE(SUM(Transactions[CashAmnt]), DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])), All(Dates), All(Symbol), All(TransType))
, SUMX(VALUES(Account[Currency])
, CALCULATE(SUM(Transactions[CashAmnt]), DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])), All(Dates), All(Symbol), All(TransType))
* var curr = Account[Currency]
var exchRate =
CALCULATE(
CALCULATE(AVERAGE(CurrencyConv[ExchRate])
, LASTNONBLANK(DatesBetween(Dates[Date], DateAdd(LastDate(Dates[Date]), -10, day), LastDate(Dates[Date]))
, CALCULATE(COUNT(CurrencyConv[ExchRate]))
)
)
, CurrencyConv[CurrencyFrom] = curr
)
RETURN IF(IsBlank(ExchRate), 1, exchRate)
)
)
)
)
, 5)
|
Total Value | ROUND([Equity Value] + [Cash Value], 2)
|
Trans Ext Symb Flow Amnt | CALCULATE([Trans Total Amnt], TransType[QtySign]=1) - CALCULATE([Trans Total Amnt], TransType[QtySign]=-1)
|
AccListCount | var repCurr = IF(NOT(HASONEVALUE(ReportCurrency[ReportCurrency])), "*Original*", VALUES(ReportCurrency[ReportCurrency]))
var accList = FILTER(Account, Account[Currency] = repCurr || repCurr = "*Original*")
RETURN COUNTROWS(accList)
|
TransExchRate | IF(NOT(HASONEVALUE(ReportCurrency[CurrencyID])), 1
, SWITCH(VALUES(ReportCurrency[CurrencyID])
, 0, 1
, 1, SUM(Transactions[ExchRate1])
, 2, SUM(Transactions[ExchRate2])
, 3, SUM(Transactions[ExchRate3])
, IF(HASONEVALUE(Account[Currency])
, var currFrom = VALUES(Account[Currency])
var exchRate =
CALCULATE(
CALCULATE(AVERAGE(CurrencyConv[ExchRate])
, LASTNONBLANK(DatesBetween(Dates[Date], DateAdd(LastDate(Dates[Date]), -10, day), LastDate(Dates[Date]))
, CALCULATE(COUNT(CurrencyConv[ExchRate]))
)
)
, CurrencyConv[CurrencyFrom] = currFrom
)
RETURN IF(isBlank(exchRate) || exchRate = 0 , 1 , exchRate)
)
))
|
Trans Ext Cash Flow Amnt | CALCULATE([Trans Total Amnt], TransType[ExternalImpactPortfolioSignCalc]=1) - CALCULATE([Trans Total Amnt], TransType[ExternalImpactPortfolioSignCalc]=-1)
|
Trans Cash Amnt | CALCULATE([Trans Total Amnt], TransType[CashAmntSign]=1) - CALCULATE([Trans Total Amnt], TransType[CashAmntSign]=-1)
|
Annlzd RoR | IF(CONTAINS(Symbol,Symbol[Symbol],"* Cash")
,
var tblTransAccnt= SUMMARIZE(Transactions , Transactions[Date], "Payment", SUMX(Transactions, Report[Trans Ext Cash Flow Amnt]))
var tblStart = ROW("Date", IF(PreviousDay(Dates[Date])=Blank(), FirstDate(Dates[Date]), PreviousDay(Dates[Date]))
, "Payment", IF(PreviousDay(Dates[Date])=Blank(), 0, CALCULATE(Report[Total Value], PreviousDay(Dates[Date])))
)
var tblEnd=Row("Date", LASTDATE(Dates[Date]), "Payment", (-1)*CALCULATE(Report[Total Value], LastDate(Dates[Date])))
var tblAccnt= UNION(FILTER(tblStart, [Payment]<>0), FILTER(tblTransAccnt, [Payment]<>0), FILTER(tblEnd, [Payment]<>0))
RETURN IF(NOT(HASONEVALUE(Symbol[Symbol])) && NOT(HASONEVALUE(Allocation[Allocation]) && CONTAINS(Allocation, [Allocation],"Cash")), IFERROR(XIRR(tblAccnt, [Payment],[Date]), BLANK()))
,
var tblStart = ROW("Date", IF(PreviousDay(Dates[Date])=Blank(), FirstDate(Dates[Date]), PreviousDay(Dates[Date]))
, "Payment", IF(PreviousDay(Dates[Date])=Blank(), 0, CALCULATE(Report[Total Value], PreviousDay(Dates[Date])))
)
var tblTransSymb= SUMMARIZE(Transactions , Transactions[Date], "Payment", SUMX(Transactions, Report[Trans Ext Symb Flow Amnt]))
var tblTransSymb2 = SUMMARIZE(Transactions, Transactions[Date], "Payment", CALCULATE(SUMX(Transactions, Report[Trans Cash Amnt]*(-1)), FILTER(TransType, AND(TransType[DividendFlag]=1,TransType[TransType]<>"DRIP")))) /* How About DripTA????? */
var tblEnd=Row("Date", LASTDATE(Dates[Date]), "Payment", (-1)*CALCULATE(Report[Total Value], LastDate(Dates[Date])))
var tblSymb = UNION(FILTER(tblStart, [Payment]<>0), FILTER(tblTransSymb, [Payment]<>0), FILTER(tblEnd, [Payment]<>0), FILTER(tblTransSymb2, [Payment]<>0))
RETURN IFERROR(XIRR(tblSymb, [Payment],[Date]), BLANK())
)
|
Cost Basis | IF(NOT(HASONEVALUE(Symbol[Symbol]) && [Qty Held]=0),
var res=
IF(NOT(HasOneValue(ReportCurrency[CurrencyID]))
, CALCULATE(SUM(Transactions[CostBasisImpact]) , DatesBetween(Dates[Date], Blank(), LastDate(Dates[Date])), ALL(Dates), All(TransType), TransType[BookValueSign]<>0)
, SWITCH(VALUES(ReportCurrency[CurrencyID])
, 1, CALCULATE(SUM(Transactions[CostBasisImpactRpt1]), DatesBetween(Dates[Date], Blank(), LastDate(Dates[Date])), ALL(Dates), All(TransType), TransType[BookValueSign]<>0)
, 2, CALCULATE(SUM(Transactions[CostBasisImpactRpt2]), DatesBetween(Dates[Date], Blank(), LastDate(Dates[Date])), ALL(Dates), All(TransType), TransType[BookValueSign]<>0)
, 3, CALCULATE(SUM(Transactions[CostBasisImpactRpt3]), DatesBetween(Dates[Date], Blank(), LastDate(Dates[Date])), ALL(Dates), All(TransType), TransType[BookValueSign]<>0)
, CALCULATE(SUM(Transactions[CostBasisImpact]) , DatesBetween(Dates[Date], Blank(), LastDate(Dates[Date])), ALL(Dates), All(TransType), TransType[BookValueSign]<>0)
)
)
RETURN IF(ROUND(res, 3) = 0, Blank(), ROUND(res, 3))
)
|
Unrlzd Cap Gain | var res=[Equity Value] - [Cost Basis] RETURN IF(ROUND(res, 2) = 0, Blank(), res)
|
Dividends | CALCULATE([Trans Total Amnt], TransType[DividendFlag]=1, ALL(Dates[Mths-Initial]))
|
Fee Mgmt $ | SUMX(Symbol, [Equity Value] * Symbol[MER])
|
Cost Basis per Unit | ROUND(DIVIDE([Cost Basis] , [Qty Held]), 6)
|
Fee Mgmt % | ROUND(DIVIDE(SUMX(Symbol, [Equity Value] * Symbol[MER]) , [Equity Value]), 4)
|
Unrlzd Cap Gain % | ROUND(DIVIDE([Unrlzd Cap Gain], [Cost Basis]), 5)
|
First Buy Date Ever | CALCULATE(MINA(Transactions[Date])
, DATESBETWEEN(Dates[Date], Blank(), MAX(Dates[Date]))
, All(Dates)
)
|
Portfolio % | DIVIDE([Total Value], CALCULATE([Total Value], ALL(Symbol)))
|
Rlzd Cap Gain w/o Sell Fee | IF([Sales Amnt] <> 0,
ROUND(
CALCULATE(
SUMX(FILTER(Transactions, NOT(IsBlank(Transactions[TransID])))
, ([TotalAmnt] + [Fee]) * [TransExchRate] +
var currID = IF(HASONEVALUE(ReportCurrency[CurrencyID]), VALUES(ReportCurrency[CurrencyID]), 0)
RETURN SWITCH(currID, 0, [CostBasisImpact], 1, [CostBasisImpactRpt1], 2, [CostBasisImpactRpt2], 3, [CostBasisImpactRpt3], [CostBasisImpact])
)
, TransType[BookValueSign] = -1, NOT(TransType[TransType] IN {"SymbolTransferIn","SymbolTransferOut"})
), 2)
)
|
Rlzd Cap Gain | IF([Sales Amnt] <> 0,
ROUND(
CALCULATE(
SUMX(FILTER(Transactions, NOT(IsBlank(Transactions[TransID])))
, [TotalAmnt] * [TransExchRate] +
var currID = IF(HASONEVALUE(ReportCurrency[CurrencyID]), VALUES(ReportCurrency[CurrencyID]), 0)
RETURN SWITCH(currID, 0, [CostBasisImpact], 1, [CostBasisImpactRpt1], 2, [CostBasisImpactRpt2], 3, [CostBasisImpactRpt3], [CostBasisImpact])
)
, TransType[BookValueSign] = -1, NOT(TransType[TransType] IN {"SymbolTransferIn","SymbolTransferOut"})
), 2)
)
|
Fee Admin | CALCULATE([Trans Total Amnt], TransType[FeeFlag] = 1)
|
Fee Trading | SUMX(Transactions, [Fee] * Report[TransExchRate])
|
Deposits | CALCULATE(
IF(VALUES(Config[TrackCash])="Yes"
, CALCULATE([Trans Total Amnt], TransType[DepositTransSign] = 1)
-CALCULATE([Trans Total Amnt], TransType[DepositTransSign] = -1)
)
, ALL(Dates[Mths-Initial])
)
|
Equity Value Start | var d = FIRSTDATE(Dates[Date]) RETURN CALCULATE(IF(NOT(ISBLANK(PREVIOUSDAY(d))), CALCULATE([Equity Value], All(Dates), PREVIOUSDAY(d))), ALL(Dates))
|
Cash Value Start | var d = FIRSTDATE(Dates[Date]) RETURN CALCULATE(IF(NOT(ISBLANK(PREVIOUSDAY(d))), CALCULATE([Cash Value], All(Dates), PREVIOUSDAY(d))), ALL(Dates))
|
Total Value Start | [Equity Value Start] + [Cash Value Start]
|
Trans Total Amnt | IF(NOT(HASONEVALUE(ReportCurrency[CurrencyID])), SUM(Transactions[TotalAmnt])
, SWITCH(VALUES(ReportCurrency[CurrencyID])
, 0, SUM(Transactions[TotalAmnt])
, 1, SUMX(Transactions, Transactions[TotalAmnt] * Transactions[ExchRate1])
, 2, SUMX(Transactions, Transactions[TotalAmnt] * Transactions[ExchRate2])
, 3, SUMX(Transactions, Transactions[TotalAmnt] * Transactions[ExchRate3])
, SUMX(SUMMARIZE(Transactions, Account[Currency], Dates[Date])
, SUM(Transactions[TotalAmnt])
* var currFrom = VALUES(Account[Currency])
var exchRate =
CALCULATE(
CALCULATE(AVERAGE(CurrencyConv[ExchRate])
, LASTNONBLANK(DatesBetween(Dates[Date], DateAdd(LastDate(Dates[Date]), -10, day), LastDate(Dates[Date]))
, CALCULATE(COUNT(CurrencyConv[ExchRate]))
)
)
, CurrencyConv[CurrencyFrom] = currFrom
)
RETURN IF(isBlank(exchRate) || exchRate = 0 , 1 , exchRate)
)
))
|
Capital Gain For Cash | IF(VALUES(Config[TrackCash]) <> "No",
IF(HASONEVALUE(ReportCurrency[ReportCurrency]) && CONTAINS(VALUES(Symbol[Symbol]), Symbol[Symbol], "* Cash")
, SUMX(FILTER(VALUES(Account[Currency]), Account[Currency] <> VALUES(ReportCurrency[ReportCurrency]))
, [Cash Value] - [Cash Value Start] - CALCULATE([Trans Cash Amnt], All(Symbol))
)
)
)
|
Value Change | [Equity Value] - [Equity Value Start] - [Trans Ext Symb Flow Amnt] + [CostBasis Adj] + IF(CONTAINS(VALUES(Symbol[Symbol]), Symbol[Symbol], "* Cash"), [Capital Gain For Cash])
|
Value Change Last Day | CALCULATE([Value Change], All(Dates), Dates[Days-Current] = "Yes")
|
Total Value by Currency | IF(NOT(HASONEVALUE(Symbol[Currency])), Blank()
, IF(VALUES(Symbol[Currency]) <> "Cash"
, [Total Value] + CALCULATE(SUMX(Account, CALCULATE([Cash Value], All(Symbol))), Account[Currency] = VALUES(Symbol[Currency]))
)
)
|
Max Quote Date | MAX(Quotes[Date])
|
Max CurrConv Date | MAX(CurrencyConv[Date])
|
Exch Rate Impact | IF(COUNTROWS(VALUES(ReportCurrency[ReportCurrency])) > 1, Blank()
, IF(VALUES(ReportCurrency[ReportCurrency]) = "*Original*", Blank()
, var fDate = IF(PreviousDay(Dates[Date])=Blank(), FirstDate(Dates[Date]), PreviousDay(Dates[Date])) -- First date in the selected period
var repCurr = VALUES(ReportCurrency[ReportCurrency])
var currListS = FILTER(VALUES(Symbol[Currency]), Symbol[Currency] <> repCurr && Symbol[Currency] <> "Cash")
var tblDtSymb1 = DISTINCT(UNION(
CALCULATETABLE(CROSSJOIN(VALUES(Dates[Date]), currListS), CALCULATETABLE(Transactions, TransType[ExternalImpactSymbolSign] = 1, currListS))
, CROSSJOIN(LASTDATE(Dates[Date]), currListS)
))
var tblDtSymb = ADDCOLUMNS(tblDtSymb1, "PrevDate", var dt = [Date] var curr=[Currency] var tDt = MAXX(FILTER(tblDtSymb1, [Date]<dt && Symbol[Currency]=curr), [Date]) RETURN IF(NOT(ISBLANK(tDt)) && tDt >= fDate, tDt, fDate))
var resSymb = SUMX(tblDtSymb
, var currFrom = [Currency] var dtLast = [Date] var dtPrev = [PrevDate]
var amnt = CALCULATE([Equity Value Orig Currency] - [Trans Ext Symb Flow Amnt Orig Currency], Dates[Date] = dtLast)
var last10Days = CALCULATETABLE(DatesBetween(Dates[Date], DateAdd(Dates[Date], -10, day), dtLast), Dates[Date] = dtLast)
var last10DaysP= CALCULATETABLE(DatesBetween(Dates[Date], DateAdd(Dates[Date], -10, day), dtPrev), Dates[Date] = dtPrev)
var exchRate = CALCULATE(CALCULATE(AVERAGE(CurrencyConv[ExchRate]), LASTNONBLANK(last10Days, CALCULATE(COUNT(CurrencyConv[ExchRate])) ) )
, CurrencyConv[CurrencyFrom] = currFrom, CurrencyConv[CurrencyTo] = repCurr)
var exchRatePrev = CALCULATE(CALCULATE(AVERAGE(CurrencyConv[ExchRate]), LASTNONBLANK(last10DaysP, CALCULATE(COUNT(CurrencyConv[ExchRate])) ))
, CurrencyConv[CurrencyFrom] = currFrom, CurrencyConv[CurrencyTo] = repCurr)
RETURN ROUND(amnt * (exchRate - ExchRatePrev), 2)
)
var currListC = FILTER(VALUES(Account[Currency]) , Account[Currency] <> repCurr)
var tblDtCash1 = DISTINCT(UNION( CALCULATETABLE(CROSSJOIN(VALUES(Dates[Date]), currListC), CALCULATETABLE(Transactions, currListC)), CROSSJOIN(LASTDATE(Dates[Date]), currListC) ))
var tblDtCash = ADDCOLUMNS(tblDtCash1, "PrevDate", var dt = [Date] var curr=[Currency] var tDt = MAXX(FILTER(tblDtCash1, [Date]<dt && Account[Currency]=curr), [Date]) RETURN IF(NOT(ISBLANK(tDt)) && tDt >= fDate, tDt, fDate))
var resCash = SUMX(tblDtCash
, var currFrom = [Currency] var dtLast = [Date] var dtPrev = [PrevDate]
var amnt = CALCULATE([Cash Value Orig Currency] - SUM(Transactions[CashAmnt]), Dates[Date] = dtLast)
var last10Days = CALCULATETABLE(DatesBetween(Dates[Date], DateAdd(Dates[Date], -10, day), dtLast), Dates[Date] = dtLast)
var last10DaysP= CALCULATETABLE(DatesBetween(Dates[Date], DateAdd(Dates[Date], -10, day), dtPrev), Dates[Date] = dtPrev)
var exchRate = CALCULATE(CALCULATE(AVERAGE(CurrencyConv[ExchRate]), LASTNONBLANK(last10Days, CALCULATE(COUNT(CurrencyConv[ExchRate])) ) )
, CurrencyConv[CurrencyFrom] = currFrom, CurrencyConv[CurrencyTo] = repCurr
)
var exchRatePrev= CALCULATE(CALCULATE(AVERAGE(CurrencyConv[ExchRate]), LASTNONBLANK(last10DaysP, CALCULATE(COUNT(CurrencyConv[ExchRate])) ))
, CurrencyConv[CurrencyFrom] = currFrom, CurrencyConv[CurrencyTo] = repCurr
)
RETURN ROUND(amnt * (exchRate - exchRatePrev), 2)
)
var res = resSymb + IF(CONTAINS(Symbol,Symbol[Symbol],"* Cash"), resCash)
RETURN IF(res=0, Blank(), res)
))
|
Alloc Actual % | ROUND(DIVIDE(Report[Total Value by Allocation] , Report[Total Value]), 6)
|
Alloc Target % | SUMX(Allocation, Allocation[TargetPercent])
|
Alloc Target | CALCULATE([Total Value], ALL(Symbol), ALL(Allocation)) * [Alloc Target %]
|
Alloc Delta | ROUND(Report[Alloc Target] - Report[Total Value by Allocation], 2)
|
Total Value by Sector | SUMX(VALUES(Symbol[Symbol]), CALCULATE(Report[Total Value] * SUM(SymbolSector[Percent])))
|
Profit | [Value Change] + [Dividends]
|
Value Change % | [Profit %] - [Dividends %]
|
Profit % | IF(CONTAINS(Symbol,Symbol[Symbol],"* Cash")
, IF(HASONEVALUE(Symbol[Symbol])
,var trDates = CALCULATETABLE(VALUES(Dates[Date]), CALCULATETABLE(Transactions, Transactions[TransType]="Int", Dates[Mths-Initial] = "No", ALL(Symbol[Symbol])))
var tr = ADDCOLUMNS(trDates, "CashValue", [Cash Value Start], "Interest", CALCULATE([Trans Total Amnt], TransType[TransType] = "Int"))
var res = PRODUCTX(tr, 1 + DIVIDE([Interest], [CashValue]))
RETURN IF(res<>0, res-1)
, var fDate = FirstDate(Dates[Date])
var fPrevDate = CALCULATE(IF(PreviousDay(fDate)=Blank(), fDate, PreviousDay(fDate)), ALL(Dates)) -- First date in the selected period
var tblDtPortf1 = DISTINCT(UNION(PreviousDay(FirstDate(Dates[Date])), CALCULATETABLE(VALUES(Dates[Date]), CALCULATETABLE(Transactions, TransType[ExternalImpactPortfolioSignCalc] <> 0, Dates[Mths-Initial] = "No", ALL(Symbol[Symbol]))) , LASTDATE(Dates[Date])))
var tblDtPortf = ADDCOLUMNS(tblDtPortf1, "PrevDate", var dt = LASTDate(Dates[Date]) var tDt = MAXX(FILTER(tblDtPortf1, [Date]<dt), [Date]) RETURN IF(NOT(ISBLANK(tDt)) && tDt >= fPrevDate, tDt, fPrevDate))
var tblPortfTrans1 = CALCULATETABLE(
ADDCOLUMNS(tblDtPortf
, "EndValue" , [Total Value]
, "ExtImpact" , [Trans Ext Cash Flow Amnt]
)
, ALL(Symbol[Symbol]) /* In holdings report we have filter [Qty Held] <>0. To go around problems with this filter we remove filter on Symbol[Symbol] */
)
var tblPortfTrans2 = FILTER(tblPortfTrans1, [EndValue] <> 0 || [ExtImpact] <> 0)
var tblPortfTrans3 = ADDCOLUMNS(tblPortfTrans2, "StartValue", var pd = [PrevDate] RETURN SUMX(FILTER(tblPortfTrans2, [Date]=pd), [EndValue]))
var tblPortfTrans = FILTER(tblPortfTrans3, [Date] >= fDate && [StartValue] + [ExtImpact] <> 0)
var valChgPortf = PRODUCTX(tblPortfTrans, [EndValue] / ([StartValue] + [ExtImpact]))
/*RETURN IF(NOT(HASONEVALUE(Symbol[Symbol])), IF(valChgPortf<>0, valChgPortf-1))*/ /* If just one symbol Cash, return empty */
RETURN IF(valChgPortf<>0, valChgPortf-1)
)
, var fDate = FirstDate(Dates[Date])
var fPrevDate = CALCULATE(IF(PreviousDay(fDate)=Blank(), fDate, PreviousDay(fDate)), ALL(Dates)) -- First date in the selected period
var tblDtSymb1 = DISTINCT(UNION(PreviousDay(FirstDate(Dates[Date])), CALCULATETABLE(VALUES(Dates[Date]), CALCULATETABLE(Transactions, FILTER(TransType, TransType[ExternalImpactSymbolSign] <> 0 || TransType[DividendFlag] = 1), Dates[Mths-Initial] = "No")), LASTDATE(Dates[Date])))
var tblDtSymb = ADDCOLUMNS(tblDtSymb1, "PrevDate", var dt = LASTDATE(Dates[Date]) var tDt = MAXX(FILTER(tblDtSymb1, [Date]<dt), [Date]) RETURN IF(NOT(ISBLANK(tDt)) && tDt >= fPrevDate, tDt, fPrevDate))
var tblSymbTrans1 =
ADDCOLUMNS(tblDtSymb
, "EndValue" , [Equity Value]
, "ExtImpact" , [Trans Ext Symb Flow Amnt] - [Dividends]
)
var tblSymbTrans2 = FILTER(tblSymbTrans1, [EndValue] <> 0 || [ExtImpact] <> 0)
var tblSymbTrans3 = ADDCOLUMNS(tblSymbTrans2, "StartValue", var pd = [PrevDate] RETURN SUMX(FILTER(tblSymbTrans2, [Date]=pd), [EndValue]))
var tblSymbTrans = FILTER(tblSymbTrans3, [Date] >= fDate && [StartValue] + [ExtImpact] <> 0)
var valChgSymb = PRODUCTX(tblSymbTrans, [EndValue] / ([StartValue] + [ExtImpact]))
RETURN IF(valChgSymb<>0, valChgSymb-1)
)
|
Dividends % | var tr = CALCULATETABLE(Transactions, TransType[DividendFlag]=1)
RETURN IF(NOT(ISEMPTY(tr)), var res = PRODUCTX(CALCULATETABLE(VALUES(Dates[Month]), tr), 1+DIVIDE([Dividends], [Total Value]))-1 RETURN IF(res=-1 || ROUND(res,5)=0, Blank(), res) )
|
Exch Rate Impact % | var res = PRODUCTX(VALUES(Dates[Month]), 1+DIVIDE([Exch Rate Impact], [Total Value]))-1 RETURN IF(res=-1 || ROUND(res,5)=0, Blank(), res)
|
Avg Mthy Dividends | DIVIDE([Dividends], COUNTROWS(VALUES(Dates[Month])))
|
Avg Mthly Deposits | DIVIDE([Deposits], COUNTROWS(VALUES(Dates[Month])))
|
CostBasis Adj | CALCULATE(SUMX(Transactions, ([TotalAmnt] - [CostBasisOverride]) * [TransExchRate]), TransType[BookValueSign]=1, Transactions[CostBasisOverride]<>0)
- CALCULATE([Trans Total Amnt], TransType[DistribCapGainReinvstdFlag] = 1)
+ CALCULATE([Trans Total Amnt], TransType[DistribReturnOfCapitalFlag] = 1)
|
Value Change Base | [Equity Value] - [Equity Value Start] - [Trans Ext Symb Flow Amnt] + IF(CONTAINS(VALUES(Symbol[Symbol]), Symbol[Symbol], "* Cash"), [Capital Gain For Cash])
|
Annlzd RoR Last 3 Yrs | CALCULATE([Annlzd RoR], Dates[Years-Last 3]="Yes", All(Dates))
|
Avg Mthly Dividends Prev Yr | CALCULATE([Avg Mthy Dividends], Dates[Years - Previous]="Yes", All(Dates))
|
Sales Amnt | CALCULATE([Trans Total Amnt], TransType[SellFlag] = 1)
|
Sales Cost Basis | IF(NOT(HasOneValue(ReportCurrency[CurrencyID])), CALCULATE(SUM(Transactions[CostBasisImpact]), TransType[SellFlag]=1)
, SWITCH(VALUES(ReportCurrency[CurrencyID]), 1, CALCULATE(SUM(Transactions[CostBasisImpactRpt1]), TransType[SellFlag]=1)
, 2, CALCULATE(SUM(Transactions[CostBasisImpactRpt2]), TransType[SellFlag]=1)
, 3, CALCULATE(SUM(Transactions[CostBasisImpactRpt3]), TransType[SellFlag]=1)
, CALCULATE(SUM(Transactions[CostBasisImpact]), TransType[SellFlag]=1)
)
) * (-1)
|
Withholding Tax Paid | CALCULATE([Trans Total Amnt], TransType[WithholdingTaxFlag] = 1)
|
Expd Profit % | IF(NOT(HASONEVALUE(CompareTo[ID])), Blank()
, SWITCH(VALUES(CompareTo[ID])
, "0", [Profit %]
, "1", var symb = VALUES(CompareTo[Symbol]) RETURN IF(symb<>"N/A",[Expd Profit % (Alloc)])
, var symb = VALUES(CompareTo[Symbol])
var lastPrice = CALCULATE(Report[Symbol Price], Symbol[Symbol]=symb, LASTDATE(Dates[Date]) , All(Dates), All(Symbol))
var prevPrice = CALCULATE(Report[Symbol Price], Symbol[Symbol]=symb, PreviousDay(Dates[Date]), All(Dates), All(Symbol))
RETURN
IF(ISBLANK(PreviousDay(Dates[Date])) || ISBLANK(symb), BLANK()
, IF(ISBLANK(lastPrice) || ISBLANK(prevPrice), Blank()
, DIVIDE(lastPrice, prevPrice) - 1
+ var adj = VALUES(CompareTo[AnnlAdj]) var dCount = COUNTROWS(Dates) RETURN IF(NOT(ISBLANK(adj)) && ISNUMBER(adj), adj / 365 * dCount)
)
)
)
)
|
Expd Profit % Diff | IF(HASONEVALUE(CompareTo[ID])
, IF(Values(CompareTo[ID]) = "0", Blank(),
var res = CALCULATE([Expd Profit %], CompareTo[ID]="0", ALL(CompareTo)) - [Expd Profit %]
RETURN IF(res=0, Blank(), res)
)
)
|
Expd Profit % (Alloc) | IF(IsBlank(PreviousDay(Dates[Date])) || (Report[Total Value] = 0 && Report[Total Value Start] = 0 && COUNTROWS(Transactions) = 0), Blank()
, IF(HASONEVALUE(Allocation[Allocation]) /* Just one allocation */
, var allocIndex = VALUES(Allocation[Index])
var lastPrice = CALCULATE([Symbol Price], Symbol[Symbol]=allocIndex, LASTDATE(Dates[Date]) , All(Dates), All(Symbol))
var prevPrice = CALCULATE([Symbol Price], Symbol[Symbol]=allocIndex, PreviousDay(Dates[Date]), All(Dates), All(Symbol))
RETURN IF(ISBLANK(lastPrice) || ISBLANK(prevPrice), Blank(), DIVIDE(lastPrice, prevPrice) - 1)
, SUMX(FILTER(Allocation, Allocation[Index] <> "" && Report[Total Value by Allocation]<>0), CALCULATE(
PRODUCTX(VALUES(Dates[Month])
, CALCULATE(
var allocIndex = VALUES(Allocation[Index])
var lastPrice = CALCULATE([Symbol Price], Symbol[Symbol]=allocIndex, LASTDATE(Dates[Date]) , All(Dates), All(Symbol))
var prevPrice = CALCULATE([Symbol Price], Symbol[Symbol]=allocIndex, PreviousDay(Dates[Date]), All(Dates), All(Symbol))
var priceChg = IF(ISBLANK(lastPrice) || ISBLANK(prevPrice), Blank(), DIVIDE(lastPrice, prevPrice) - 1)
var tValue = [Total Value by Allocation]
var tValueAll = [Total Value]
RETURN IF(ISBLANK(lastPrice) || ISBLANK(prevPrice) || ISBLANK(tValue), Blank(), 1 + priceChg * DIVIDE(tValue, tValueAll))
)
) - 1
)
)
)
)
|
Cash Value Orig Currency | ROUND(IF(VALUES(Config[TrackCash])="Yes"
, IF(CONTAINS(Symbol,Symbol[Symbol],"* Cash")
, CALCULATE(SUM(Transactions[CashAmnt]), DATESBETWEEN(Dates[Date], BLANK(), LastDate(Dates[Date])), All(Dates), All(Symbol), All(TransType))
)
)
, 5)
|
Equity Value Orig Currency | SUMX(Symbol , var qty = [Qty Held] RETURN IF(qty<>0, qty * [Symbol Price Orig Currency]))
|
Trans Total Amnt Orig Currency | SUMX(Transactions, [TotalAmnt])
|
Trans Ext Symb Flow Amnt Orig Currency | CALCULATE([Trans Total Amnt Orig Currency], TransType[QtySign]=1) - CALCULATE([Trans Total Amnt Orig Currency], TransType[QtySign]=-1)
|
Avg Yrly Dividends | DIVIDE([Dividends], COUNTROWS(VALUES(Dates[Month]))) * 12
|
Avg Yrly Dividends % | DIVIDE([Dividends %], COUNTROWS(VALUES(Dates[Month]))) * 12
|
Profit % to date | CALCULATE([Profit %], DATESBETWEEN(Dates[Date], Blank(), LASTDATE(Dates[Date])))
|
Symbol Price (Exists) | IF([Qty Held]<>0, [Symbol Price])
|
Total Value by Allocation | SUMX(VALUES(Allocation[Allocation]), SUMX(CALCULATETABLE(VALUES(Symbol[Symbol]), SymbolAllocation), CALCULATE(VALUES(SymbolAllocation[Percent])) * [Total Value]))
|
LastRefresh | VALUES('Config'[RefreshTime])
|
Deposits Avg To Date | var my=CALCULATE(MAX(Dates[Year]), Dates[Mths-Initial]<>"Yes") RETURN IF(my=BLANK(), BLANK(), CALCULATE(AVERAGEX(VALUES(Dates[Year]), [Deposits]), ALL(Dates), Dates[Year]<=my, Dates[Mths-Initial] <> "Yes"))
|