Moving average based on prior N rows

[StephaneMA]

I am trying to calculate a moving average based on the prior N rows of a table. Something that would match the table below.

I have tried the following but it does not provide the desired result:

MOVING Average submissions by N week =

AVERAGEX(

                FILTER(

                            TOPN(

                                        6,'Normalized Calendar', [Normalized Week],DESC), ('Normalized Calendar'[Normalized Week]

                                      )

                           ),

                  'Opportunity'[Opportunity Count])

Any help pointing me in the right direction would be greatly appreciated.

Thank you!

[mahoneypat · Accepted solution]

The comma goes before the All().  Try doing just All('Normalized Calendar') instead (remove the column reference).

Regards,

Pat

[nandukrishnavs]

@StephaneMA 

Try this DAX measure.

Moving Avg =
VAR n = 5
VAR current =
    SELECTEDVALUE ( Table[weeknumber] )
VAR avg =
    CALCULATE (
        AVERAGE ( Table[Subs] ),
        FILTER (
            ALL ( Table[weeknumber] ),
            Table[weeknumber] <= current
                && Table[weeknumber] >= current - n
        )
    )
RETURN
    avg

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[StephaneMA]

Thank you nandukrishnavs,

I appreciate the suggestion. I get exactly the same result than I did with Pat's suggestion above.

[nandukrishnavs]

@StephaneMA 

Weeknumber	Subs
1	282
2	508
3	540
4	518
5	717
6	599
7	622
8	416
9	603
10	622
11	730
12	617
13	618

Moving Avg = 
VAR n = 5
VAR _selectedweekno =
    SELECTEDVALUE ( 'Table'[Weeknumber] )
VAR result =
    CALCULATE (
        AVERAGE ( 'Table'[Subs] ),
        FILTER (
            ALL ( 'Table'[Weeknumber] ),
            'Table'[Weeknumber] <= _selectedweekno
                && 'Table'[Weeknumber] >= _selectedweekno - n
        )
    )
RETURN
   IF(_selectedweekno>=n,result,BLANK())

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[StephaneMA]

I appreciate the help but I do not get the same result at all. I created a new table with the same data to replicate your formula but I get bery different results....

I am not sure at this point what I am doing wrong to get this result.

[nandukrishnavs · Accepted solution]

@StephaneMA colum type is whole number/decimal. Can you copy the table which i shared above and create the same measure (not calculated column) without any  edits. Also share the snapshot.?

[StephaneMA]

That did it.......!

Thank you nandukrishnavs.

There must be something wromng with the original dataset. I will fix that issue now that I have a working average working!

I really appreciate your assistance.

[mahoneypat]

Prior 5 Weeks =
VAR currentweeknumber =
SELECTEDVALUE ( Table[Normalized Week Number] )
RETURN
CALCULATE (
AVERAGE ( Table[Subs])
ALL ( Table[Normalized Week Number] ),
Table[Normalized Week Number] <= currentweeknumber,
Table[Normalized Week Number] >= currentweeknumber - 5
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[StephaneMA]

Thank you Pat,

I appreciate the quick reply.

When I enter the code as sent, I get the following:

If I add a coma before the ALL function, the error disappears but the result is not what I expect (see below)

Can you think of what is creating the issue?

Again, I really appreciate your assistance.

SL

[mahoneypat · Accepted solution]

The comma goes before the All().  Try doing just All('Normalized Calendar') instead (remove the column reference).

Regards,

Pat

[StephaneMA]

Pat,

thank you for the help......you had it all along....there must be something wrong with the original dataset which affected the result.

I appreciate your assistance!

Thank you again

[StephaneMA]

Sorry Pat, 

Gave the same result

Prior 5 Weeks =

VAR currentweeknumber =

SELECTEDVALUE ( 'Normalized Calendar'[Normalized Week] )

RETURN

CALCULATE (

AVERAGE (Opportunity[SL@ Opportunity Count]), ALL ('Normalized Calendar'),

'Normalized Calendar'[Normalized Week] <= currentweeknumber,

'Normalized Calendar'[Normalized Week] >= currentweeknumber - 5

)

Best

SL

[mahoneypat]

Well that's unexpected (at least to me).  Numbers look right beyond Week 5.  Are there Opportunity data raws before Normalized Week 1?  I will take a long again tomorrow, if someone else doesn't solve it first.

Regards,

Pat