Average and Moving average

[tomgag]

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?

Week	Schedule	Unschedule	Ave Schedule	Ave Unschedule	MA 3 Weeks (Schedule)	MA 3 Weeks (Unschedule)
1	100	100	125	124
2	110	110	125	124
3	120	120	125	124
4	130	130	125	124	330	330
5	140	140	125	124	360	360
6	60	100	125	124	390	390
7	160	110	125	124	330	370
8	170	120	125	124	360	350
9	180	130	125	124	390	330
10	80	180	125	124	510	360
11					430	430

[v-juanli-msft · Accepted solution]

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]

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