Help with "dynamic" moving average

[Anonymous]

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

[anil]

Hi Ricardo,

             I have worked on the similar requirement, please follow the link.

https://community.powerbi.com/t5/Desktop/Moving-Average-Last-3-days-per-category-ID/td-p/465310

[Anonymous]

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]

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]

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 · Accepted solution]

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] )
        )
    )

Best Regards,

Dale

[Ashish_Mathur]

Hi,

Why do you need the Moving Average in a calculated column?  Why not in a measure?