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 Measure > Moving Average)

 

The normal cases are for id's like 1 and 2; but for cases like id 3 and 4 admitting that there are no entries, i would calculate to calculate the Moving Average with the entries that i have (in this case, 0 entries for id 4, and 2 entries for id 3)

 

Thanks!

Regards

 

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

6 Replies

    • 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