Calculate rolling average for future 7 days

[anandita]

Hi . I want to generate forecast which is 7 days rolling average of past days. This forecast should be generated for next 7 days based on latest date in [MV_START_DT_NEW] column . The average should be generated by grouping cluster , plant and product description. Below is the data sample and expected output columns highlighted in yellow

Cluster_new	GSAP_PLANT_MAPPED	Product Description	MV_START_DT_NEW	Sum_Inv (BBL)	Forecasted value	Future forecast dates
NYHNE	Y826	ETHANOL	5/17/2024	-119.3809524	10.33721088	5/24/2024
NYHNE	Y826	ETHANOL	5/18/2024	-133.5714286	10.33721088	5/25/2024
NYHNE	Y826	ETHANOL	5/19/2024	-134.8333333	10.33721088	5/26/2024
NYHNE	Y826	ETHANOL	5/20/2024	-137.7380952	10.33721088	5/27/2024
NYHNE	Y826	ETHANOL	5/21/2024	-195.4761905	10.33721088	5/28/2024
NYHNE	Y826	ETHANOL	5/22/2024	920.6904762	10.33721088	5/29/2024
NYHNE	Y826	ETHANOL	5/23/2024	-127.33	10.33721088	5/30/2024
RG3SW	Y001	PUL	5/17/2024	108.5952381	5.64	5/24/2024
RG3SW	Y001	PUL	5/18/2024	-39.04761905	5.64	5/25/2024
RG3SW	Y001	PUL	5/19/2024	-30.88095238	5.64	5/26/2024
RG3SW	Y001	PUL	5/20/2024	26.4047619	5.64	5/27/2024
RG3SW	Y001	PUL	5/21/2024	-173.047619	5.64	5/28/2024
RG3SW	Y001	PUL	5/22/2024	193.0238095	5.64	5/29/2024
RG3SW	Y001	PUL	5/23/2024	-124.547619	5.64	5/30/2024
COLO	Y01Q	DIESEL	5/17/2024	-401.7857143	149	5/24/2024
COLO	Y01Q	DIESEL	5/18/2024	-70.88095238	149	5/25/2024
COLO	Y01Q	DIESEL	5/19/2024	-122.1904762	149	5/26/2024
COLO	Y01Q	DIESEL	5/20/2024	-129.6428571	149	5/27/2024
COLO	Y01Q	DIESEL	5/21/2024	-193.2619048	149	5/28/2024
COLO	Y01Q	DIESEL	5/22/2024	-44.61904762	149	5/29/2024
COLO	Y01Q	DIESEL	5/23/2024	-83.70952381	149	5/30/2024

[Anonymous · Accepted solution]

Hi @anandita,

You can try to use the following measure formulas if it sutiabel for your requirement:

Rolling AVG =
VAR currDate =
    MAX ( Table1[MV_START_DT_NEW] )
RETURN
    AVERAGEX (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( Table1 ), [MV_START_DT_NEW] <= currDate - 7 ),
            [Cluster_new],
            [GSAP_PLANT_MAPPED],
            [Product Description],
            "Total", SUM ( Table[Sum_Inv (BBL)] )
        ),
        [Total]
    )

Regards,

Xiaoxin Sheng

[anandita]

Thank you for responding @Anonymous . However I am not able to plot this measure and getting below error