How to insert visualization of Annualized ROR

CNH

Hi,

I am trying to insert a line chart that shows the annualised ROR on a day to day basis.

The following measure shows the accumulated ROR.

Annlzd RoR = 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(Trans, Trans[Date], "Payment", SUMX(Trans, Report[Trans Ext Symb Flow Amnt]))
var tblTransSymb2 = SUMMARIZE(Trans, Trans[Date], "Payment", CALCULATE(SUMX(Trans, Report[Trans Cash Impact]*(-1)), FILTER(TransType, AND(TransType[DividendFlag]=1,TransType[TransType]<>"DRIP"))))
var tblTransAccnt= SUMMARIZE(Trans, Trans[Date], "Payment", SUMX(Trans, Report[Trans Ext Cash Flow Amnt]))

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))
var tblAccnt= UNION(FILTER(tblStart, [Payment]<>0), FILTER(tblTransAccnt, [Payment]<>0), FILTER(tblEnd, [Payment]<>0)) 

RETURN IF(CONTAINS(Symbol,Symbol[Symbol],"* Cash")
		, IF(NOT(HASONEVALUE(Symbol[Symbol])) && NOT(HASONEVALUE(Symbol[Allocation]) && CONTAINS(Symbol, Symbol[Allocation],"Cash")), IFERROR(XIRR(tblAccnt, [Payment],[Date]), BLANK()))
		, IFERROR(XIRR(tblSymb, [Payment],[Date]), BLANK())
	)

I haven't been able to transform this into a plotting on a line chart. It appears that the data that plots is the day to day ROR. The following is an extract of some of the data





I have tried to muddle my way through but keep on going around in circles. Do not be afraid to say research the question elsewhere - I know you spend heaps of time in support, so I don't want to push to hard.

VidasMatelis

Annlzd RoR returns Annualized Rate of Return for selected Date period. As you are using Date in one chart axis, you are getting Daily Annlzd RoR and that does not make sense.
Actually, daily or even monthly Annzd RoR value is too volatile to be used. For example, you have a 100$ worth of share that increased today by 1%. Annlzd RoR value will be around 365%+ as it "annualizes" value - that is assumes that every day (365 days) in the year return will be same 1%, You can observe that volatility by checking Annzld RoR for specific Year at the first days of the year.
Maybe it would make more sense for you to use Profit%?

What sort of info you are looking for in that chart?

CNH

Hi Vidas, I am trying to plot the annualised ROR against The Market ROR. The attached graph is from my existing excel spreadsheet that I have been tracking my portfolio on. I have been working my way through Portfolio Slicer and populating the data.

I am now turning my attention to the reporting, by adding a few extra visualisations to your PowerBI file.

The attached excel sheet shows an extract of dataset from my original excel worksheet.

Any suggestions/help will be greatly appreciated.

VidasMatelis

Hi,

Can we say that you are you looking for Year to Date (YTD) returns?

CNH

Yes, that is correct

VidasMatelis

In this case you would have to create new measures that shows YTD values, example:

Profit % YTD = CALCULATE([Profit %], DATESYTD(Dates[Date]))

Annlzd RoR % YTD = CALCULATE([Annlzd RoR], DATESYTD(Dates[Date]))

CNH

Vidas, that is what I am seeking. THANK YOU.

One more question, if I can:

DATESYTD(Dates[Date]) - defaults to December 31, how do I change this so that the fiscal year is 1 July to 30 June

CNH

Hi,

I have created a new date column as follows
Fiscal Year = if(MONTH([DATE]) > 6, YEAR([DATE])+1, YEAR([DATE]))

When I change the code you provided to Annlzd RoR % YTD = CALCULATE([Annlzd RoR], DATESYTD(Dates[Fiscal Year])) I get the following error message

Error Message:
MdxScript(Model) (517, 59) Calculation error in measure 'Report'[Annlzd RoR % YTD]: A column specified in the call to function 'DATESYTD' is not of type DATE. This is not supported.
;

Sorry, I am a complete novice. Any help will be welcome.

VidasMatelis

I still think that Profit % YTD would be a better measure for you. But, for your current setup you should use a formula that specifies Fiscal year end:

Annlzd RoR % YTD = CALCULATE([Annlzd RoR], DATESYTD(Dates[Date], "06-30"))

More info about DatesYTD function: https://msdn.microsoft.com/query-bi/dax/datesytd-function-dax

CNH

Hi Vidas,

Thanks for your help. Yes you are very correct the Profit%YTD is exactly what I need and emulates what I have on my current excel worksheet.

I had read the DatesYTD function reference that you provided. However my head wasn't able to understand how to insert the "06-30" parameter. Again thank you.