Forum Discussion

ak77's avatar
ak77
Icon for Post Patron rankPost Patron
2 years ago

Row Count for Annualized (rolling) returns

Hi All, Thanks for all help for my queries till date. Please advise on the below Issue.   I have a big data set for multiple portfolio's starting from year 2010 . i am calculating rolling returns...
Show and Tell
Tips and Tricks
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

ak77 , based on what I got

 

Anualized =
var _sum = CALCULATE(SUM ( Client_Returns_Data[Returns]),DATESINPERIOD('Date Table'[_Date], [_Start_Date], -2,YEAR))
var _date= CALCULATE(Distinctcount ( Client_Returns_Data[retur_date]),DATESINPERIOD('Date Table'[_Date], [_Start_Date], -2,YEAR))
return
divide(_sum, _date)*365

 

 

or

 

CALCULATE(Averagex(Values('Date Table'), CALCULATE(SUM ( Client_Returns_Data[Returns]))),DATESINPERIOD('Date Table'[_Date], [_Start_Date], -2,YEAR))

ak77's avatar
ak77
Icon for Post Patron rankPost Patron
2 years ago

Thanks amitchandak . wil try.

 

have a question: is there a way to check  if data is avaialble for the full year before calculating this ?

 

 CALCULATE(SUM ( Client_Returns_Data[Returns]))),DATESINPERIOD('Date Table'[_Date], [_Start_Date], -2,YEAR))

  • ak77's avatar
    ak77
    Icon for Post Patron rankPost Patron
    2 years ago

    Hi amitchandak ,

    Need a help,for one client i have both montlhy and daily data , Monthly data till  <2021 and Daily Data from 2021

     

    for Effective date 31/12/2022 , i need to check row count as below for each year and then calculate if rowcounts are satisifed... how to get this.. can u please check and help

     

    YearRowcount
    1 year- 2022365
    2 year 2022,2021365+ 12
    3 year 2022,2021,2020365+ 12 +12