Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    " 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's avatar
lbendlin
Super User
5 years ago

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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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(.