Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Help with "dynamic" moving average

Hi, How can i calculate the moving average for the last 3 (group by id) events, dynamically in a calculated column in Tabular? (The final objective is to create a new column Tendency and seeing if ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    Try this formula and the demo in the attachment, please. 

    Column =
VAR currentDate2 = Table1[Date2]
VAR ids =
    CALCULATE (
        COUNT ( Table1[id] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[id] ), Table1[Date2] <= currentDate2 )
    )
RETURN
    IF (
        ids < 4,
        BLANK (),
        CALCULATE (
            AVERAGEX (
                FILTER (
                    SUMMARIZE (
                        'Table1',
                        Table1[id],
                        Table1[Date2],
                        Table1[Value],
                        "ids2", CALCULATE (
                            COUNT ( Table1[id] ),
                            FILTER (
                                ALLEXCEPT ( Table1, Table1[id] ),
                                Table1[Date2] <= EARLIER ( Table1[Date2] )
                            )
                        )
                    ),
                    [ids2] < ids
                        && [ids2]
                        >= ids - 3
                ),
                [Value]
            ),
            ALLEXCEPT ( Table1, Table1[id] )
        )
    )

    Help_with_dynamic_moving_average

     

    Best Regards,

    Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi anil and v-jiascu-msft,

 

a small change on the original table... what i'm searching for is this:

 


Before 3 entries (for each id), no result (as we are calculating Mov Average "3 entries" before); after that, calculate Moving Average always for the 3 previous dates, for each id.
 

Thanks!

Regards

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

Try this formula and the demo in the attachment, please. 

Column =
VAR currentDate2 = Table1[Date2]
VAR ids =
    CALCULATE (
        COUNT ( Table1[id] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[id] ), Table1[Date2] <= currentDate2 )
    )
RETURN
    IF (
        ids < 4,
        BLANK (),
        CALCULATE (
            AVERAGEX (
                FILTER (
                    SUMMARIZE (
                        'Table1',
                        Table1[id],
                        Table1[Date2],
                        Table1[Value],
                        "ids2", CALCULATE (
                            COUNT ( Table1[id] ),
                            FILTER (
                                ALLEXCEPT ( Table1, Table1[id] ),
                                Table1[Date2] <= EARLIER ( Table1[Date2] )
                            )
                        )
                    ),
                    [ids2] < ids
                        && [ids2]
                        >= ids - 3
                ),
                [Value]
            ),
            ALLEXCEPT ( Table1, Table1[id] )
        )
    )

Help_with_dynamic_moving_average

 

Best Regards,

Dale