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
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?

      • Max01's avatar
        Max01
        Helper I
        8 years ago

        Hello,

         

        I've managed to reproduce my data, please see below. The first table is the raw data and the second table is what I'm hoping to achieve in powerapps but have been so far unsuccsessful:

         

        Raw Data

        delay_enddelay_length
        17/10/201725
        17/10/201710
        23/10/201760
        23/10/201760
        24/10/2017100
        26/10/20175
        26/10/201740
        26/10/201710
        26/10/201720
        26/10/201720
        27/10/201715
        26/10/201710
        28/10/201730
        28/10/201710
        28/10/201710
        30/10/201720
        30/10/2017390
        01/11/201720
        03/11/201730
        06/11/201720
        06/11/2017130
        06/11/2017120
        06/11/201790
        06/11/201740
        09/11/201735
        09/11/201715
        09/11/201715
        16/11/201715
        17/11/2017190

         

        Average calculated as =sum(last 5 days delay)/5

        delay_enddelay_lengthdelay_length_average
        17/10/201735 
        18/10/20170 
        19/10/20170 
        20/10/20170 
        21/10/201707
        22/10/201700
        23/10/201712024
        24/10/201710044
        25/10/2017044
        26/10/201710565
        27/10/20171568
        28/10/20175054
        29/10/2017034
        30/10/2017410116
        31/10/2017095
        01/11/20172096
        02/11/2017086
        03/11/20173092
        04/11/2017010
        05/11/2017010
        06/11/201740086
        07/11/2017086
        08/11/2017080
        09/11/20176593
        10/11/2017093
        11/11/2017013
        12/11/2017013
        13/11/2017013
        14/11/201700
        15/11/201700
        16/11/2017153
        17/11/201719041

         

         

        I hope that makes more sense now and someone will be able to help.

  • 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