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 ...
Need Help
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.

    • SivaMani's avatar
      SivaMani
      Resident Rockstar
      8 years ago

      Max01,

       

      Why don't you try quick measure?

       

      • Max01's avatar
        Max01
        Helper I
        8 years ago

        Thank you very much for the suggestions, the quick measure worked for creating a chart but still shows the same data in the table(?). The data is currently stored in sharepoint and there's a bug stopping me exporting to excel (MS are aware and looking into it). Is it possible to export raw data from powerbi?

         

        However this has highlighted another issue. Days where there are no delays reported should be counted as a delay of 0 (because the numnber of minutes of delay that day was 0).

         

        Can PowerBI take this into account?

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi Max01

       

      Could you paste some raw data here with expected results or share the PBIX file with expected results....

       

      This way you will hopefully get a QUICK Solution from someone