Forum Discussion

tomgag's avatar
tomgag
Resolver I
7 years ago
Solved

Average and Moving average

Hi All, I need help with some basic stuff. I fried my brain and hit the dead end.   So, how can I add simple average for the whole period and 3 weeks MA to my data to get the below in bold columns?...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi tomgag

    Based on my test, the "Ave Schedule" and "Ave Unschedule" provided by Greg_Deckler are right.

    Then, Try the following formula to get calculated columns "3 weeks MA"

    MA 3 Weeks (Schedule) =
IF (
    [Week] > 3,
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            [Week] < EARLIER ( [Week] )
                && [Week]
                    >= EARLIER ( [Week] ) - 3
        ),
        [Schedule]
    )
)

MA 3 Weeks (Schedule) =
IF (
    [Week] > 3,
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            [Week] < EARLIER ( [Week] )
                && [Week]
                    >= EARLIER ( [Week] ) - 3
        ),
        [Unschedule]
    )
)

    Best Regards

    Maggie

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

The first two are easy:

 

Column Ave Schedule = AVERAGEX(ALL('Table'),[Schedule])
Column Ave Unschedule = AVERAGEX(ALL('Table'),[Unschedule])

The second two I don't understand because those look like sum's and not averages but probably something like:

 

Column MA 3 Weeks (Schedule) =
SUMX(FILTER(ALL('Table'),[Week]<EARLIER([Week])&&[Week]>=EARLIER([Week])-2),[Schedule])