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, thank you very much for your help, unfortunately i now know that my source is a snapshot table, so i need to find a way to remove adjecent values for measures...

     

    See [yellow] id 1 in particular:

     

    Is there any way to do this?

     

    regards

     

     

     

     

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

      Hi Anonymous,

       

      Would all the adjacent values be removed? If so, should the 3 in blue be (3 + 0 + 0) / 3?

       

      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