Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate rolling average for future 7 days

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 ...
Data Modeling
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous,

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous,

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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