PRODUCTX is not working for Cumulative Return

Question

I am trying to add the filtered return totals for each day and then use the DAX pattern to accumulate returns&nbsp; (cumulative return).&nbsp; I've tried several in-line variation of the below code, but nothing seems to&nbsp;function correctly except MINX/MAXX/SUMX.&nbsp; I am working in DirectQuery mode.&nbsp; The calculate table appears to force a summation and then add the 1 at the end.&nbsp; Ultimately I want to use this measure in a "Line and Stacked Column" chart to view returns across varying dimentions.&nbsp;Cumulative Return % = 
	VAR maxDate = MAX([Return Date])
	VAR minDate = MIN([Return Date])
	VAR DAILYSUM = CALCULATETABLE(SUMMARIZE(ALLSELECTED('Dates'), [Return Date], "Ret%", SUM('Exposure And Return'[Return %])), 'Exposure And Return'[Return Date] &lt;= maxDate)   
    Return PRODUCTX(DAILYSUM, 1 + [Ret%]) - 1 This produces the&nbsp;following results across a date range in June.&nbsp; As you can see, the result is just a sum of the Return % line.&nbsp;Return DateCumulative Return %Return %6/1/20180.0350%0.0350%6/4/20180.0375%0.0025%6/5/2018-0.0124%-0.0499%6/6/2018-0.0058%0.0066%6/7/2018-0.1102%-0.1044%6/8/2018-0.0473%0.0629%6/11/20180.0220%0.0693%6/12/20180.0570%0.0350%6/13/20180.0806%0.0236%6/14/20180.0462%-0.0344%6/15/20180.0451%-0.0011%6/18/20180.1033%0.0582%6/19/20180.0430%-0.0602%6/20/20180.0477%0.0047%6/21/20180.0056%-0.0421%6/22/20180.1037%0.0981%6/25/20180.0626%-0.0411%6/26/2018-0.0066%-0.0691%6/27/20180.0342%0.0408%&nbsp;Any help would be greatly appreciated!

v-jiascu-msft · Accepted Answer

Hi&nbsp;Kevin-Mann,
&nbsp;
It seems you need a number that could be the total of Return. But you didn't write it in the formula. How about the one like below?
Cumulative Return % = 
	VAR maxDate = MAX([Return Date])
	VAR minDate = MIN([Return Date])
	VAR DAILYSUM = CALCULATETABLE(SUMMARIZE(ALLSELECTED('Dates'), [Return Date], "Ret%", SUM('Exposure And Return'[Return %])), 'Exposure And Return'[Return Date] &lt;= maxDate)   
    Return PRODUCTX(DAILYSUM, [Ret%] * [Return Amount]) 
&nbsp;
Best Regards,
Dale
&nbsp;

Forum Discussion

PRODUCTX is not working for Cumulative Return

2 Replies

Related content

Cumulative Total

ProductX not working with measure (alternative)

cumulative 5 day return (productx)

Creating Cumulative Graphs

Year to date cumulative

Return Date	Cumulative Return %	Return %
6/1/2018	0.0350%	0.0350%
6/4/2018	0.0375%	0.0025%
6/5/2018	-0.0124%	-0.0499%
6/6/2018	-0.0058%	0.0066%
6/7/2018	-0.1102%	-0.1044%
6/8/2018	-0.0473%	0.0629%
6/11/2018	0.0220%	0.0693%
6/12/2018	0.0570%	0.0350%
6/13/2018	0.0806%	0.0236%
6/14/2018	0.0462%	-0.0344%
6/15/2018	0.0451%	-0.0011%
6/18/2018	0.1033%	0.0582%
6/19/2018	0.0430%	-0.0602%
6/20/2018	0.0477%	0.0047%
6/21/2018	0.0056%	-0.0421%
6/22/2018	0.1037%	0.0981%
6/25/2018	0.0626%	-0.0411%
6/26/2018	-0.0066%	-0.0691%
6/27/2018	0.0342%	0.0408%