Cumulative Product for last X months - based on Date From Separate Table

[Wendeley-North]

Hi, I've currently two tables that I need to put together, joined by a common Date table. For each date selected by TableFE, create various measures that take the cumulative product of the column [ReturnsNorm] for 1M, 3M and 6M lookback.

TableFE (Simplified to only have one date)

Date	SlicerGroup1	SlicerGroup2	ParentGroup	Group	Value
08-Jun-21	SG1_1	SG2_1	PG1	PG1_G1	0.340
08-Jun-21	SG1_1	SG2_1	PG1	PG1_G2	0.030
08-Jun-21	SG1_1	SG2_1	PG2	PG2_G1	-0.388
08-Jun-21	SG1_1	SG2_1	PG2	PG2_G2	-0.430

TableFR

Date	SlicerGroup1	ParentGroup	Group	Return	ReturnsNorm
29-Jan-21	SG1_1	PG_2	PG2_G1	0.0048	1.0048
26-Feb-21	SG1_1	PG_2	PG2_G1	-0.0069	0.9931
31-Mar-21	SG1_1	PG_2	PG2_G1	-0.0031	0.9969
30-Apr-21	SG1_1	PG_2	PG2_G1	-0.0030	0.9970
31-May-21	SG1_1	PG_2	PG2_G1	-0.0267	0.9733
29-Jan-21	SG1_1	PG_2	PG2_G2	0.0020	1.0020
26-Feb-21	SG1_1	PG_2	PG2_G2	-0.0056	0.9944
31-Mar-21	SG1_1	PG_2	PG2_G2	0.0052	1.0052
30-Apr-21	SG1_1	PG_2	PG2_G2	-0.0010	0.9990
31-May-21	SG1_1	PG_2	PG2_G2	0.0214	1.0214
29-Jan-21	SG1_1	PG_1	PG1_G2	0.0003	1.0003
26-Feb-21	SG1_1	PG_1	PG1_G2	0.0035	1.0035
31-Mar-21	SG1_1	PG_1	PG1_G2	-0.0030	0.9970
30-Apr-21	SG1_1	PG_1	PG1_G2	-0.0024	0.9976
31-May-21	SG1_1	PG_1	PG1_G2	-0.0100	0.9900
29-Jan-21	SG1_1	PG_1	PG1_G1	0.0002	1.0002
26-Feb-21	SG1_1	PG_1	PG1_G1	-0.0059	0.9941
31-Mar-21	SG1_1	PG_1	PG1_G1	-0.0002	0.9998
30-Apr-21	SG1_1	PG_1	PG1_G1	-0.0028	0.9972
31-May-21	SG1_1	PG_1	PG1_G1	0.0018	1.0018

Table Relationship

Desired Results

Applicable Slicers: Date (8 June 21 Selected), SlicerGroup1 selected

For the 6M lookback, since the provided sample data has only 5 months of data, the cumulative product is as such.

Group	Value	1M Lookback	3M Lookback	6M Lookback
PG1_G1	0.340	1.0018	0.9988	0.9931
PG1_G2	0.030	0.9900	0.9847	0.9884
PG2_G1	-0.388	0.9733	0.9674	0.9653
PG2_G2	-0.430	1.0214	1.0257	1.0220

Attempted Code

1M_date = 
VAR endDate = EOMONTH( LASTDATE(TableFE[Date]), -1 ) -- Determined by slicer
VAR startDate = EOMONTH( LASTDATE(TableFE[Date]), -1 )
-- VAR startDate = DATE( YEAR(endDate), MONTH(endDate) - 1, DAY(endDate) ) -- 3-month lookback
VAR filteredTable = 
    FILTER( 
        ALLEXCEPT( TableFR, TableFR[Group] ), 
        TableFR[Date] >= startDate &&
        TableFR[Date] <= endDate 
        )
VAR t1 =
    SUMMARIZE( filteredTable, TableFR[Factor], "CumRet", PRODUCT( TableFR[ReturnsNorm] ) )
RETURN 
    [CumRet]

Which doesn't work because returning columns doesn't work like that. 

Appreciate any help, thanks. 

[Kumail]

Hello @Wendeley-North 

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here.

Regards
Kumail Raza

[v-yetao1-msft]

Hi @Wendeley-North 

Has your problem been solved ? If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

If no , please answer my previous doubts, and then we will continue to deal with this issue .

Best Regards

Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi @Wendeley-North 

I have a little doubt about the Desired Results you provided .

For PG1_G1 , The data for the previous month, 3 months, and 6 months are 1.0018, 0.9988, and 0.9931 respectively , meet the expectations you want .

But for the remaining 3 groups, the values of the first three months do not match what you expected, so what are your calculation rules for the values of the first 3 months and the first 6 months? Please confirm the results you need, and if possible, provide your sample. This will make it easier for us to deal with the problem better .

Actual results:

Desired Results

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wendeley-North]

Bump