Forum Discussion

nielfxrm's avatar
nielfxrm
Frequent Visitor
7 years ago

3 months moving average

I need some help on calculating a moving average. I've read a dozen of posts on the forum and seens multiple videos on YouTube but I'm not able to get it working. 

 

I've tried different methods but I'm clearely missing out on something. 

 

The Close Date in the Opportunity Table is related to my Calendar Table. 

 

MovingAvg1:

 

CALCULATE (
    AVERAGEX ( Opportunity; Opportunity[Weighted Sales Value (DKK)]);
    DATESINPERIOD (
        Opportunity[Close Date];
        LASTDATE ( Opportunity[Close Date] );
        -3;
        MONTH
    )
)

MovingAvg2:

 

 

CALCULATE (
    SUM ( Opportunity[Sales Price (DKK)]);
    DATESINPERIOD ( Opportunity[Close Date]; LASTDATE ( Opportunity[Close Date] ); -3; MONTH )
)/
CALCULATE (
    DISTINCTCOUNT( Opportunity[Close Date]);
    DATESINPERIOD ( Opportunity[Close Date]; LASTDATE ( Opportunity[Close Date] ); -3; MONTH )
)

This is what it looks like right now:

 

 

And this is what I hoped it would look like:

Any input is appreciated. 

Need Help

3 Replies

  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    nielfxrm

    Without the actual PowerBI workspace it will be a bit difficult to recreate the formula.

    Could you please attached the PowerBI workspace to have a look at it?

     

    Alternative on this website you can donwload the PowerBI document and see the formula the guy is using to calculate the moving average

    • nielfxrm's avatar
      nielfxrm
      Frequent Visitor
      7 years ago

      Thanks for your reply, themistoklis. 

       

      I need to anonymize the data before I can upload the file. I'll see if that is possible. 

      I had a look at the link you provided. It seems that a quick measure has been used. I've tried that as well, but that doesn't work either.