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?

 

WeekScheduleUnscheduleAve ScheduleAve UnscheduleMA 3 Weeks (Schedule)MA 3 Weeks (Unschedule)
1100100125124  
2110110125124  
3120120125124  
4130130125124330330
5140140125124360360
660100125124390390
7160110125124330370
8170120125124360350
9180130125124390330
1080180125124510360
11    430430
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

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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])