How to calculate a rolling product formula based on date slicer

[dannytango]

Hi, 

I'm trying to calculate to do the product calculation to each row, but I can only get it to calculate from the beginning of dataset instead of calculating based on the slicer date.  As you can see in the first picture and the second picture.  In the second picture after the slicer is applied, it should start calculating from the beginning again and the cumulative result should be 94.8%.  

Thank you for your help.

[v-eqin-msft · Accepted solution]

Hi @dannytango ,

According to my understand, you want to calculate the accumulate of measure dynamically based on Slicer, right?

You could follow these steps:

1. Create a new table with Date for Slicer.

2. Use the following formula:

test =
VAR _min =
    MIN ( 'forSlicer'[Date] )
VAR _max =
    MAX ( 'forSlicer'[Date] )
RETURN
    CALCULATE (
        PRODUCTX ( TCMMonthlyReturnsInput, TCMMonthlyReturnsInput[MRTCM] + 1 ),
        FILTER (
            TCMMonthlyReturnsInput,
            'TCMMonthlyReturnsInput'[Date] >= _min
                && 'TCMMonthlyReturnsInput'[Date] <= MAX ( 'TCMMonthlyReturnsInput'[Date] )
        )
    ) - 1

 3. Apply the measure below to filter pane( set =1) 

Measure =
VAR _min =
    MIN ( 'forSlicer'[Date] )
VAR _max =
    MAX ( 'forSlicer'[Date] )
RETURN
    IF (
        MAX ( 'TCMMonthlyReturnsInput'[Date] ) >= _min
            && MAX ( 'TCMMonthlyReturnsInput'[Date] ) <= _max,
        1,
        0
    )

4. Calculate the accumulate value:

Final Accumulate test =
CALCULATE (
    SUMX ( 'TCMMonthlyReturnsInput', [test] ),
    FILTER (
        ALL ( 'TCMMonthlyReturnsInput' ),
        'TCMMonthlyReturnsInput'[Date] <= MAX ( 'TCMMonthlyReturnsInput'[Date] )
            && [Measure] = 1
    )
)

 My visualization looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[dannytango]

The code returned the calculation total correctly, but doesn't calculate for each row.  Couldn't upload pbix file, so I have to put paste it into table below.  Thanks.

Date	MRTCM
1/31/2017	0.66%
2/28/2017	0.21%
3/31/2017	0.96%
4/30/2017	0.66%
5/31/2017	5.21%
6/30/2017	4.20%
7/31/2017	9.14%
8/31/2017	8.62%
9/30/2017	3.12%
10/31/2017	14.91%
11/30/2017	3.09%
12/31/2017	-0.26%
1/31/2018	11.04%
2/28/2018	4.16%
3/31/2018	-7.44%
4/30/2018	-2.48%
5/31/2018	24.14%
6/30/2018	0.99%
7/31/2018	-0.97%
8/31/2018	8.98%
9/30/2018	0.84%
10/31/2018	-16.97%
11/30/2018	-7.69%
12/31/2018	-31.07%
1/31/2019	23.02%
2/28/2019	0.24%
3/31/2019	8.83%
4/30/2019	10.56%
5/31/2019	-23.15%
6/30/2019	29.09%
7/31/2019	10.17%
8/31/2019	-7.13%
9/30/2019	2.98%
10/31/2019	8.71%
11/30/2019	3.65%
12/31/2019	3.76%
1/31/2020	7.39%
2/29/2020	0.98%
3/31/2020	-3.48%
4/30/2020	24.12%
5/31/2020	8.34%
6/30/2020	7.90%
7/31/2020	6.49%
8/31/2020	3.00%
9/30/2020	0.01%

[dannytango]

Thanks, @AllisonKennedy 

Sorry, but I'm pretty new to DAX so I don't think I've applied your advice properly.  This is my attempt following your advice:

TCMCumulative test =
var startDate = MIN(TCMMonthlyReturnsInput[Date]) return

CALCULATE(PRODUCTX(TCMMonthlyReturnsInput,TCMMonthlyReturnsInput[MRTCM]+1),
ALLSELECTED(TCMMonthlyReturnsInput[Date],TCMMonthlyReturnsInput[MRTCM]),
FILTER(TCMMonthlyReturnsInput,startdate<=MAX(TCMMonthlyReturnsInput[Date])))-1

This got me the final result calculated right, but each row is still not being calculated, it just reiterating the original data.

Thanks again for your help.

[AllisonKennedy]

Try updating to something like this;
TCMCumulative test =
VAR startDate =
    CALCULATE (
        MIN ( TCMMonthlyReturnsInput[Date] ),
        ALLSELECTED ( TCMMonthlyReturnsInput[Date] )
    )
RETURN
    CALCULATE (
        PRODUCTX (
            FILTER ( TCMMonthlyReturnsInput, startdate <= TCMMonthlyReturnsInput[Date] ),
            TCMMonthlyReturnsInput[MRTCM] + 1
        ),
        ALLSELECTED ( TCMMonthlyReturnsInput[Date], TCMMonthlyReturnsInput[MRTCM] )
    ) - 1


If that still doesn't work, can you please provide sample data?

[AllisonKennedy]

You will need to define the MAX(Date) as a variable before your ProductX, this ensures you get the max date prior to adding the row context that the ProductX function gives of row by row.

That also means you'll likely need to experiment with ALLSELECTED within the MaxDate variable.

Let us know if you need more info or resources on variables, otherwise hopefully that helps.