Forum Discussion

Max01's avatar
Max01
Helper I
8 years ago

Rolling Average

Hello,

 

I've been reading a lot on this and tried so many different permutations but can't get it to work.

 

Goal: I am trying to plot a moving average for 'delays' against date. Here is my raw data (which is connected to my date table):

 

Data

 

Here is the code im using:

 

delay length avg = calculate( 

Average('Production Delays'[delay_length]),

DATESINPERIOD (
        'Dates'[Date].[Date],
        LASTDATE ( Dates[Date].[Date] ),
        -5,
        DAY
    )
)

I realise I don't have daily data but I would still like the X day average.

 

Can you help?

 

Thanks in advance :)

Need Help

8 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Max01,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    delay length avg =
CALCULATE (
    SUM ( 'Production Delays'[delay_length] ),
    DATESINPERIOD ( 'Dates'[Date], MAX ( Dates[Date] ), -5, DAY )
)
    / 5

     

     

    Regards

    • Max01's avatar
      Max01
      Helper I
      8 years ago

      Hi v-ljerr-msft,

       

      Thank you for coming back with a solution and for taking the time to replicate my data.

       

      Unfortunately it's still not working, now I get a blank column:

       

       

      I tried increasing the increment to 10 days and then to 1 month but the column remains blank.

       

      Any ideas?

       

      • Max01's avatar
        Max01
        Helper I
        8 years ago

        I would be very grateful if anyone could help me solve this.