It looks like you're new here. If you want to get involved, click one of these buttons!
Expd Return % (TMTR)
=IF((Report[Total Value]<> 0 || Report[Start Value] <> 0 || COUNTROWS(Trans)>0)
, DIVIDE(
CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Config[TMTRIndex]), LASTDATE(Dates[Date]), All(Dates), All(Symbol))
, IF(PreviousDay(Dates[Date]) = Blank()
, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Config[TMTRIndex]), FIRSTDATE(Dates[Date]), All(Dates), All(Symbol))
, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Config[TMTRIndex]), PreviousDay(Dates[Date]), All(Dates), All(Symbol))
)
) - 1
)
Expd Return % (Alloc)
=IF(PreviousDay(Dates[Date]) <> Blank() && (Report[Total Value]<> 0 || Report[Start Value] <> 0 || COUNTROWS(Trans)>0)
, IF(COUNTROWS(VALUES(Symbol[Allocation]))=1
, IF(CALCULATE(VALUES(Allocation[Index]), FILTER(Allocation, Allocation[Allocation] = VALUES(Symbol[Allocation]))) <> ""
, CALCULATE(
IF(CALCULATE(Report[Symbol Price], LASTDATE(Dates[Date]), All(Dates))<> 0 && CALCULATE(Report[Symbol Price], PreviousDay(Dates[Date]), All(Dates)) <> 0
,DIVIDE(
CALCULATE(Report[Symbol Price], LASTDATE(Dates[Date]), All(Dates))
, IF(PreviousDay(Dates[Date]) = Blank()
, CALCULATE(Report[Symbol Price], FIRSTDATE(Dates[Date]), All(Dates))
, CALCULATE(Report[Symbol Price], PreviousDay(Dates[Date]), All(Dates))
)
) - 1
)
, ALL(Symbol)
, FILTER(ALL(Symbol), Symbol[Symbol] = CALCULATE(VALUES(Allocation[Index]), FILTER(ALL(Allocation), Allocation[Allocation] = VALUES(Symbol[Allocation]))))
)
)
, SUMX(FILTER(Allocation, Allocation[Index] <> "" && Report[Total Value]<>0)
, CALCULATE(EXP(SUMX(FILTER(VALUES(Dates[Month]), Report[Total Value] <> 0
&& CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), LASTDATE(Dates[Date]), All(Dates), All(Symbol)) <> 0
&& CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), PreviousDay(Dates[Date]), All(Dates), All(Symbol)) <> 0
)
, LN(1 +
CALCULATE(
(DIVIDE(CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), LASTDATE(Dates[Date]), All(Dates), All(Symbol))
, IF(PreviousDay(Dates[Date]) = Blank()
, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), FIRSTDATE(Dates[Date]), All(Dates), All(Symbol))
, CALCULATE(Report[Symbol Price], Symbol[Symbol]=VALUES(Allocation[Index]), PreviousDay(Dates[Date]), All(Dates), All(Symbol))
)
) - 1
)
* ROUND(DIVIDE(CALCULATE(Report[Total Value], All(Symbol), VALUES(Allocation[Allocation]), VALUES(Symbol[Allocation]))
, CALCULATE(Report[Total Value], All(Allocation), All(Symbol)))
, 2)
)
)
)) - 1)
)
)
)
Compare To Value
=Report[Start Value] +
IF((Contains(VALUES(SymbolSector[Sector]), SymbolSector[Sector], "* Cash") || Not ISCROSSFILTERED(SymbolSector[Sector])) && CONTAINS(Symbol,Symbol[Symbol],"* Cash")
, IF(HASONEVALUE(Symbol[Symbol])
, CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[CashFlag]=1)
+CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[DividendFlag]=1, ALL(Symbol))
, CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[DepositTransSign]<>0)
)
, CALCULATE(SUMX(Trans, Trans[TotalAmnt] * Report[TransReportExchRate]), TransType[BookValueSign]=1)
)
Comments
Thanks
At the first line of each formula there is a name of the measure.
Formula start with symbol '=' and you should replace old formula from where it starts with '='.
Keep in the mind that for Excel 2016 one measure formula is slightly different - second post in this thread.
Can you provide raw instruction on where to edit formulas in the Excel 2016. Is the option in the ribbon ? Does i need to edit the field directly ? I cant seem to view the formulas used.
Sorry but you are the Excel wizard here
http://portfolioslicer.com/docs/excel2013-powerpivot.html
If you have full PowerPivot support, then you will see (after enabling) menu "Power Pivot". There will be button "Measures" and choose "Manage Measures..." option and then select measures you want to edit.
I note that the PowerBI file in the download area is dated (2019-Jan-11) and shows the tag <<<***New***>>>.
Can I check if this file is different to previous PowerBI file.
The reason I ask is that I have modified/tailored the PowerBI for my own reporting purposes, to migrate the PowerBI is a bigger job for me
PowerBI uses same formula as Excel 2016. So for Expd Return % (Alloc) use formula in second post.