Forum Discussion

ak77's avatar
ak77
Post 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 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.

 

 

 

 

 

 

 

 

Show and Tell
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
      Post 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
        Post 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