Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tomgag
Resolver I
Resolver I

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
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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"

2.png

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

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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"

2.png

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
Super User
Super User

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.