Row Count for Annualized (rolling) returns

[ak77]

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 from 1 - 10 years based on user selected date as below.for some portfolio, the data is monthly wise(12 per year) and some portfolio are daily wise(365 per year).  

DAX used for Ex for 2 year: 2 year=CALCULATE(SUM ( Client_Returns_Data[Returns]),DATESINPERIOD('Date Table'[_Date], [_Start_Date], -2,YEAR))

Now the issue is, i  want to check the row count and then calculate the annualized year. for 2 year for a selected date..

i have to check if its year wise or day wise count for each year.

How to put condition to the DAX formula to check this rowcount? please help.

[amitchandak]

@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]

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]

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

Year	Rowcount
1 year- 2022	365
2 year 2022,2021	365+ 12
3 year 2022,2021,2020	365+ 12 +12