ProductX Iterative Calculation

[ARob198]

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 Return	MTD Int	ITD Return
Jan	0.71%	100.71%	0.71%
Feb	-3.58%	96.42%	-2.90%
Mar	-4.33%	95.67%	-7.10%
Apr	7.99%	107.99%	0.32%
May	2.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)

)

[Anonymous]

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]

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!

Date	MTD Return	MTD Int	ITD Return
1/31/2020	0.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/2020	7.99%	107.99%	0.32%
5/31/2020	2.81%	102.81%	3.14%

[Anonymous · Accepted solution]

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!

[ARob198]

Hi Kelly v-kellya-msft

This works fine in my test cases, but when I put it into my actual model, I keep getting an error that EARLIER/EARLIEST refers to an earlier row context that does not exist.  What does that error mean?  If I do it as a measure, I get the Earlier/Earliest error.  If I do it as a calculated column, I get an error that there is a circular reference.  Do you have any ideas on how to solve this?

ITD Return =

PRODUCTX(FILTER(

ALL('FundInpValues'), 'FundInpValues'[DATE] <= EARLIER('FundInpValues'[DATE])), 'FundInpValues'[Fund MTD Int]) -1

ITD Return =

PRODUCTX(FILTER(

ALL(FundInpValues), FundInpValues[DATE] <= EARLIER(FundInpValues[DATE])), FundInpValues[Fund MTD Int]) -1

[ARob198]

Thank you- it worked!