Rolling Average

[Max01]

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):

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 🙂

[v-ljerr-msft]

Hi @Max01,

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

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

 

Regards

[Max01]

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]

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

[SivaMani]

@Max01,

Why don't you try quick measure?

[Max01]

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]

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_end	delay_length
17/10/2017	25
17/10/2017	10
23/10/2017	60
23/10/2017	60
24/10/2017	100
26/10/2017	5
26/10/2017	40
26/10/2017	10
26/10/2017	20
26/10/2017	20
27/10/2017	15
26/10/2017	10
28/10/2017	30
28/10/2017	10
28/10/2017	10
30/10/2017	20
30/10/2017	390
01/11/2017	20
03/11/2017	30
06/11/2017	20
06/11/2017	130
06/11/2017	120
06/11/2017	90
06/11/2017	40
09/11/2017	35
09/11/2017	15
09/11/2017	15
16/11/2017	15
17/11/2017	190

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

delay_end	delay_length	delay_length_average
17/10/2017	35
18/10/2017	0
19/10/2017	0
20/10/2017	0
21/10/2017	0	7
22/10/2017	0	0
23/10/2017	120	24
24/10/2017	100	44
25/10/2017	0	44
26/10/2017	105	65
27/10/2017	15	68
28/10/2017	50	54
29/10/2017	0	34
30/10/2017	410	116
31/10/2017	0	95
01/11/2017	20	96
02/11/2017	0	86
03/11/2017	30	92
04/11/2017	0	10
05/11/2017	0	10
06/11/2017	400	86
07/11/2017	0	86
08/11/2017	0	80
09/11/2017	65	93
10/11/2017	0	93
11/11/2017	0	13
12/11/2017	0	13
13/11/2017	0	13
14/11/2017	0	0
15/11/2017	0	0
16/11/2017	15	3
17/11/2017	190	41

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

[Anonymous]

I need to solve exactly the same issue. Have you found a sollution?

[Zubair_Muhammad]

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