Annualised rolling return - DAX/Calculated Column

[JamesB86]

Good Day

Not sure if this is possible or not - I have a data stream of average returns over time and I wanted to create a calculated column or a measure which can calculate the annualised returns for the 1Y/3Y/5Y periods for each row, so it essentially calculates the respective return in line with the date it is on the row of. 

Below is some generated data with some dates, I want to calculate what the 1Y annualised rate of return is from each of these date points where there are 12 points of data (1Years worth) so 30 Sep 2017-31 Oct 2016, 31 Aug 2017 - 30 Sep 2016, 31 Jul 2017 - 31 Aug 2016 etc etc. 

   

The general formula i've been working with is:

Column = CALCULATE(

PRODUCTX(Data_tables_AVG,1+Data_tables_AVG[Avg_Returns])-1,

DATESINPERIOD(CalendarTable[Date],MAX(CalendarTable[Date]), -1, YEAR))

But it seems it only returns the top Years worth of data (ie 1 years worth - see previous screenshot titled "column") and I'm pretty sure the results are incorrect. 

- Data_tables_AVG is the table set

- AVG_Returns is in the 1st screenshot

- CalendarTable[Date] is my Date table (see below screenshot) linked by a many to one relationship

In excel i would just do a {=Product(1+RANGE:RANGE)-1} exponent function and drag it about. 

N.B. For 3Y & 5Y versions I would need to do some form of ^(12/36) or ^(12/60) to annualise it. 

Thank you for any assistance in advance!

[lbendlin · Accepted solution]

" I can see you seem to have got it to report at month end level"

I use the calendar date for the X axis but I have a visual filter set 

That way only days that have data are considered for rendering in the visual. The measure still works, even for dates that have no data.

"To confirm - each row in the table you screenshotted in your output has the 'average' of the prior 12/36/60 months of data from the corresponding date point? "

Correct.

[lbendlin]

"I assume this should be 'Single' with CalendarTable as the primary table?"

Correct

"Could I simply modify the 'AVERAGEX' here with 'PRODUCTX' "

I guess so. I don't understand that whole compounding part (not a finance person) so you're on your own here I'm afraid.

[JamesB86]

I will have a go with the productx function, shouldnt be terrible to factor about.

However I can see you seem to have got it to report at month end level, for some reason mine will only report daily (I assume because of my Calender Table setup?). I tried converting it to a date heirarchy to counter this but it wont generate month end - do you have my PBIX updated with your calculations so I might compare with the full file?

To confirm - each row in the table you screenshotted in your output has the 'average' of the prior 12/36/60 months of data from the corresponding date point? 

Thank you again for your help this seems to be working so far, once I get the dates working/PRODUCTX functioning I can post results in here and close.

[lbendlin]

[lbendlin]

I noticed that you have bidirectional search enabled for your relationship. That is not advisable.

Is the final outcome expected to be a list of months and their 1Y, 3Y and 5Y averages?

1Y = 
var d = SELECTEDVALUE(CalendarTable[Date])
return AVERAGEX(filter(All(Avg_Returns),Avg_Returns[Date]<=d && Avg_Returns[Date]>EDATE(d,-12)),Avg_Returns[Avg_Returns])

3Y = 
var d = SELECTEDVALUE(CalendarTable[Date])
return AVERAGEX(filter(All(Avg_Returns),Avg_Returns[Date]<=d && Avg_Returns[Date]>EDATE(d,-36)),Avg_Returns[Avg_Returns])

5Y = 
var d = SELECTEDVALUE(CalendarTable[Date])
return AVERAGEX(filter(All(Avg_Returns),Avg_Returns[Date]<=d && Avg_Returns[Date]>EDATE(d,-60)),Avg_Returns[Avg_Returns])

[JamesB86]

Hi

@lbendlin 

I noticed that you have bidirectional search enabled for your relationship. That is not advisable.

---

Sorry I'm still learning bits - is this in the manage relationships tooltip where it has "cross filter direction" set to 'Both'? I assume this should be 'Single' with CalendarTable as the primary table?

Is the final outcome expected to be a list of months and their 1Y, 3Y and 5Y averages?

The final outcome needs to be the annualised values (ie list.product([Avg_Returns])-1) but for 1/3/5 year. The reason it needs to be 'Annualized Total Return' is because it captures the effects of compounding in the calculation where average does not. (((1+value1) * (1+ value2) * (1+value3 ))etc) I have circumvented the need for 1+ by just doing an addition on the columns prior to this calculated column. 

In your syntax; Could I simply modify the 'AVERAGEX' here with 'PRODUCTX' ? For 3 & 5 Year I think I would need to surround the calculate with POWER(. 

[JamesB86]

@lbendlin I've uploaded the PBIX to my googledrive along with an excel file with expected results , however in terms of expected results I dont mind if these come out in columns/seperate table or can just be a measure itself that would plot to a line chart.

https://drive.google.com/drive/folders/10MPYi78juYMAe92Z2rnx6cVPgirW9vDc?usp=sharing 

(can't share it directly here for some reason) 

Let me know if you have any issues accessing. Thank you again

[lbendlin]

Please provide sample data in usable format (not as a picture) and show the expected outcome.