Forum Discussion
Annualised rolling return - DAX/Calculated Column
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:
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!
" 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.
8 Replies
" 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.
Please provide sample data in usable format (not as a picture) and show the expected outcome.
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
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])Hi
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(.
"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.
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.