Forum Discussion

StephaneMA's avatar
StephaneMA
Frequent Visitor
6 years ago
Solved

Moving average based on prior N rows

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 desi...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

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

    Regards,

    Pat

     

  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    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's avatar
StephaneMA
Frequent Visitor
6 years ago

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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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

Regards,

Pat

 

  • StephaneMA's avatar
    StephaneMA
    Frequent Visitor
    6 years ago

    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's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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

       

  • StephaneMA's avatar
    StephaneMA
    Frequent Visitor
    6 years ago

    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