Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
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
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?
I would be very grateful if anyone could help me solve this.
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?
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.
I need to solve exactly the same issue. Have you found a sollution?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |