Forum Discussion

ARob198's avatar
ARob198
Icon for Helper IV rankHelper IV
6 years ago
Solved

ProductX Iterative Calculation

Hello,

 

I have a data set where I have the following information.  I have a date column with dates by month.  I also have a MTD return and a column that is the MTD Return +1.  I want to create a measure which iterates the product of all of the MTD Return +1 from the start of the model up until the current date minus 1.  I believe that this formula should work, but I am getting Infinity values. Does anyone know how to get this back on track?  Thank you.

 

I have pasted a sample and the ITD Return has what should be the correct result. 

 

 MTD ReturnMTD IntITD Return
Jan0.71%100.71%0.71%
Feb-3.58%96.42%-2.90%
Mar-4.33%95.67%-7.10%
Apr7.99%107.99%0.32%
May2.81%102.81%3.14%

 

I have been using this formula. 

 

ITD Return = VAR x =
MAX('FundInpValues'[DATE])
RETURN
CALCULATE((
(PRODUCTX('FundInpValues', if(('FundInpValues'[MTD Return %]>-1), [MTD Int], 1)))
-1),
ALLSELECTED('FundInpValues'),
'FundInpValues'[DATE] <= x, 'FundInpValues'[DATE] > DATE(2016,12,31)
)
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi ARob198 ,

     

    Create a calculated column as below:

     

    ITD Return = PRODUCTX(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])),'Table'[MTD Int])-1

     

    And you will see:

     

    Or you can create a measure as below:

     

    _ITD Return = PRODUCTX(FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),'Table'[MTD Int])-1

     

    And you will see:

     

    For the related .pbix file,pls click here.


     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ARob198 ，

     

    Could you pls update your sample data with [date] column?

    For column MTD Int,the logic is  1+MTD Return,right?

    But how to get column ITD Return?Can you advise me more about the details?

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • ARob198's avatar
      ARob198
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi Kelly Anonymous ,

       

      The month is the date column.  It is actually the month end date.  ITD is the product of MTD Int for all periods <=  date minus 1.  So for example, Feb ITD is ((100.71% * 96.42%)  - 1).  March ITD is ((100.71% * 96.42% * 95.67)  - 1).  I am not sure why my formula is returning infinity.  Do you know how I can fix this?  Thank you so much!

       

      DateMTD ReturnMTD IntITD Return
      1/31/20200.71%100.71%0.71%
      2/29/2020-3.58%96.42%-2.90%
      3/31/2020-4.33%95.67%-7.10%
      4/30/20207.99%107.99%0.32%
      5/31/20202.81%102.81%3.14%
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi ARob198 ,

         

        Create a calculated column as below:

         

        ITD Return = PRODUCTX(FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])),'Table'[MTD Int])-1

         

        And you will see:

         

        Or you can create a measure as below:

         

        _ITD Return = PRODUCTX(FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])),'Table'[MTD Int])-1

         

        And you will see:

         

        For the related .pbix file,pls click here.


         
        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!