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.
Hi,
I would like to calculate a rolling average per 30 minutes in large amount of data. Every minute a machine registers data. I want to see an average value over the PAST 30 minutes in THAT specific minute. So for example in minute 4 - an average of the past 30 minutes in values and in minute 57 - an average of the past 30 minutes in values.
I hope someone can help me with this.
The example that i am working on:
215063 | 5-8-2022 00:06 | 0 |
215064 | 5-8-2022 00:07 | 0 |
215065 | 5-8-2022 00:08 | 4,23 |
215066 | 5-8-2022 00:09 | 12 |
215067 | 5-8-2022 00:10 | 9,89 |
215068 | 5-8-2022 00:11 | 0 |
215069 | 5-8-2022 00:12 | 0 |
215070 | 5-8-2022 00:13 | 0 |
215071 | 5-8-2022 00:14 | 0 |
215072 | 5-8-2022 00:15 | 8,12 |
215073 | 5-8-2022 00:16 | 8,89 |
215074 | 5-8-2022 00:17 | 10,12 |
215075 | 5-8-2022 00:18 | 11,45 |
215076 | 5-8-2022 00:19 | 0 |
215077 | 5-8-2022 00:20 | 0 |
215078 | 5-8-2022 00:21 | 0 |
215079 | 5-8-2022 00:22 | 0 |
215080 | 5-8-2022 00:23 | 0 |
215081 | 5-8-2022 00:24 | 0 |
215082 | 5-8-2022 00:25 | 0 |
215083 | 5-8-2022 00:26 | 0 |
215084 | 5-8-2022 00:27 | 1,89 |
215085 | 5-8-2022 00:28 | 0 |
215086 | 5-8-2022 00:29 | 0 |
215087 | 5-8-2022 00:30 | 0 |
215088 | 5-8-2022 00:31 | 0 |
215089 | 5-8-2022 00:32 | 0 |
215090 | 5-8-2022 00:33 | 0 |
215091 | 5-8-2022 00:34 | 0 |
215092 | 5-8-2022 00:35 | 1,82 |
Solved! Go to Solution.
Hi @Anonymous
Please refer to attached file with the solution.
Rolling Average =
VAR CurrentTime = Data[TimeStamp]
VAR PreviousTime = CurrentTime - TIME ( 0, 30, 0 )
VAR FilteredTable = FILTER ( Data, Data[TimeStamp] <= CurrentTime && Data[TimeStamp] > PreviousTime )
RETURN
AVERAGEX ( FilteredTable, Data[Value] )
Hi @Anonymous
Please refer to attached file with the solution.
Rolling Average =
VAR CurrentTime = Data[TimeStamp]
VAR PreviousTime = CurrentTime - TIME ( 0, 30, 0 )
VAR FilteredTable = FILTER ( Data, Data[TimeStamp] <= CurrentTime && Data[TimeStamp] > PreviousTime )
RETURN
AVERAGEX ( FilteredTable, Data[Value] )
That is exactly what I need. I tried this and I still have one issue though...
Is there something I can do about it?
@Anonymous
The provided code is a calculated column not a measure. If you are looking for a measure, please check the sample file and amend it witht the eaxpexcted visual then share it with me.
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 |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |