The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I want to calculate the 15 min moving average for the below data. Can you please help? Also, it should calculate only for the present day. Please help.
Solved! Go to Solution.
I'm not too familiar with Direct Query or its limitations. If you want a measure, I guess you could place
Table1[Timestamp] in the x axis of a chart and modify the previous code to use as measure.
Measure = VAR MinsInWindow_ = 15 //Change window lenght as required (in minutes) VAR HigherEnd_ = SELECTEDVALUE(Table1[Time Stamp]) //Read the current time. That's the higher end of the window for the averaging. VAR LowerEnd_ = HigherEnd_ - MinsInWindow_ / ( 24 * 60 ) // Determine the lower end of the window 1/(24*60) represents one minute)
RETURN CALCULATE ( //Calculate the average in the 15 min window AVERAGE ( Table1[PR] ), Table1[Time Stamp] <= HigherEnd_, Table1[Time Stamp] > LowerEnd_, ALL ( Table1 ) )
Hi @Kolumam
Try a calculated column as below. You don't provide data in a format that can be copied, so i haven't tested it.
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
NewColumn = VAR MinsInWindow_ = 15 //Change window lenght as required (in minutes) VAR HigherEnd_ = Table1[Time Stamp] VAR LowerEnd_ = HigherEnd_ - MinsInWindow_ / ( 24 * 60 ) RETURN CALCULATE ( AVERAGE ( Table1[PR] ), Table1[Time Stamp] <= HigherEnd_, Table1[Time Stamp] > LowerEnd_, ALL ( Table1 ) )
Hi @AlB
Thank you for your answer. Please find the below sample data. Could you please help your code with the help of comments as I am finding it difficult to understand. I am using direct query so probably cannot use functions such as MAX/CALCULATE/AVERAGE.
Time Stamp PR 19/8/2019 5:47 400.00% 19/8/2019 5:48 38.50% 19/8/2019 5:49 25.00% 19/8/2019 5:51 111.10% 19/8/2019 5:52 55.60% 19/8/2019 5:53 133.30% 19/8/2019 5:54 230.80% 19/8/2019 5:55 100.00% 19/8/2019 5:56 39.20% 19/8/2019 5:57 100.00% 19/8/2019 5:58 80.70% 19/8/2019 5:59 75.80% 19/8/2019 6:00 97.80% 19/8/2019 6:01 75.40% 19/8/2019 6:02 78.90% 19/8/2019 6:03 81.80% 19/8/2019 6:04 87.30% 19/8/2019 6:05 84.20% 19/8/2019 6:06 83.20% 19/8/2019 6:07 92.40% 19/8/2019 6:08 92.80% 19/8/2019 6:09 96.80% 19/8/2019 6:10 92.40% 19/8/2019 6:11 88.90% 19/8/2019 6:12 88.60% 19/8/2019 6:13 88.90% 19/8/2019 6:14 89.40% 19/8/2019 6:15 83.90% 19/8/2019 6:16 78.70% 19/8/2019 6:17 89.60% 19/8/2019 6:18 93.90% 19/8/2019 6:19 89.80% 19/8/2019 6:20 102.00% 19/8/2019 6:21 99.40% 19/8/2019 6:22 103.50% 19/8/2019 6:23 100.70% 19/8/2019 6:24 97.40% 19/8/2019 6:25 107.00% 19/8/2019 6:26 105.60% 19/8/2019 6:27 108.50% 19/8/2019 6:28 107.00% 19/8/2019 6:29 104.10% 19/8/2019 6:30 104.80% 19/8/2019 6:31 103.30% 19/8/2019 6:32 108.30% 19/8/2019 6:33 109.30% 19/8/2019 6:34 107.50% 19/8/2019 6:35 103.40% 19/8/2019 6:36 108.30% 19/8/2019 6:37 111.40% 19/8/2019 6:38 106.30% 19/8/2019 6:39 104.30% 19/8/2019 6:40 103.70% 19/8/2019 6:41 105.50% 19/8/2019 6:42 104.00% 19/8/2019 6:43 99.40% 19/8/2019 6:44 103.40% 19/8/2019 6:45 105.60% 19/8/2019 6:46 107.60% 19/8/2019 6:47 104.00% 19/8/2019 6:48 106.90% 19/8/2019 6:49 106.90% 19/8/2019 6:50 112.20% 19/8/2019 6:51 103.10% 19/8/2019 6:52 104.80% 19/8/2019 6:53 103.30% 19/8/2019 6:54 103.50% 19/8/2019 6:55 102.80% 19/8/2019 6:56 100.40% 19/8/2019 6:57 100.50% 19/8/2019 6:58 99.30% 19/8/2019 6:59 99.90% 19/8/2019 7:00 99.80% 19/8/2019 7:01 91.90% 19/8/2019 7:02 97.10% 19/8/2019 7:03 97.80% 19/8/2019 7:04 96.40% 19/8/2019 7:05 95.70% 19/8/2019 7:06 100.80% 19/8/2019 7:07 94.60% 19/8/2019 7:08 94.60% 19/8/2019 7:09 95.20% 19/8/2019 7:10 95.10% 19/8/2019 7:11 94.20% 19/8/2019 7:12 91.20% 19/8/2019 7:13 92.70% 19/8/2019 7:14 92.00% 19/8/2019 7:15 85.70% 19/8/2019 7:16 89.60% 19/8/2019 7:17 91.00% 19/8/2019 7:18 90.00% 19/8/2019 7:19 90.20% 19/8/2019 7:20 88.70% 19/8/2019 7:21 94.00% 19/8/2019 7:22 89.00% 19/8/2019 7:23 89.20% 19/8/2019 7:24 88.70% 19/8/2019 7:25 87.70% 19/8/2019 7:26 80.80% 19/8/2019 7:27 87.90% 19/8/2019 7:28 87.50% 19/8/2019 7:29 87.30% 19/8/2019 7:30 85.30% 19/8/2019 7:31 87.10% 19/8/2019 7:32 90.70% 19/8/2019 7:33 85.20% 19/8/2019 7:34 84.80% 19/8/2019 7:35 84.80% 19/8/2019 7:36 84.70% 19/8/2019 7:37 84.20% 19/8/2019 7:38 78.20% 19/8/2019 7:39 82.60% 19/8/2019 7:40 83.10% 19/8/2019 7:41 83.20% 19/8/2019 7:42 83.00% 19/8/2019 7:43 88.60% 19/8/2019 7:44 82.50% 19/8/2019 7:45 81.50% 19/8/2019 7:46 82.20% 19/8/2019 7:47 76.20% 19/8/2019 7:48 82.30% 19/8/2019 7:49 82.40% 19/8/2019 7:50 81.70% 19/8/2019 7:51 81.30% 19/8/2019 7:52 87.60% 19/8/2019 7:53 81.20% 19/8/2019 7:54 81.50% 19/8/2019 7:55 80.80% 19/8/2019 7:56 82.90% 19/8/2019 7:57 92.50% 19/8/2019 7:58 123.70% 19/8/2019 7:59 182.90% 19/8/2019 8:00 296.20% 19/8/2019 8:01 267.30% 19/8/2019 8:02 166.70% 19/8/2019 8:03 109.70% 19/8/2019 8:04 93.40% 19/8/2019 8:05 74.90% 19/8/2019 8:06 84.20% 19/8/2019 8:07 79.30% 19/8/2019 8:08 78.80% 19/8/2019 8:09 80.00% 19/8/2019 8:10 79.70% 19/8/2019 8:11 81.10% 19/8/2019 8:12 80.10% 19/8/2019 8:13 79.80% 19/8/2019 8:14 73.80% 19/8/2019 8:15 65.10% 19/8/2019 8:16 75.50% 19/8/2019 8:17 86.70% 19/8/2019 8:18 75.80% 19/8/2019 8:19 86.80% 19/8/2019 8:20 79.80% 19/8/2019 8:21 65.80% 19/8/2019 8:22 70.50% 19/8/2019 8:23 66.10% 19/8/2019 8:24 57.40% 19/8/2019 8:25 66.70% 19/8/2019 8:26 155.90% 19/8/2019 8:27 80.50% 19/8/2019 8:28 248.80% 19/8/2019 8:29 262.80% 19/8/2019 8:30 225.50% 19/8/2019 8:31 69.20% 19/8/2019 8:32 54.40% 19/8/2019 8:33 56.70% 19/8/2019 8:34 113.50% 19/8/2019 8:35 103.60% 19/8/2019 8:36 147.40% 19/8/2019 8:37 51.80% 19/8/2019 8:38 55.50% 19/8/2019 8:39 171.00% 19/8/2019 8:40 246.30% 19/8/2019 8:41 59.90% 19/8/2019 8:42 64.20% 19/8/2019 8:43 69.00% 19/8/2019 8:44 77.90% 19/8/2019 8:45 78.20% 19/8/2019 8:46 77.20% 19/8/2019 8:47 75.90% 19/8/2019 8:48 73.30% 19/8/2019 8:49 70.80% 19/8/2019 8:50 74.60% 19/8/2019 8:51 74.50% 19/8/2019 8:52 76.90% 19/8/2019 8:53 76.50% 19/8/2019 8:54 86.30% 19/8/2019 8:55 77.70% 19/8/2019 8:56 76.10% 19/8/2019 8:57 66.00% 19/8/2019 8:58 68.70% 19/8/2019 8:59 74.90% 19/8/2019 9:00 73.30% 19/8/2019 9:01 75.40% 19/8/2019 9:02 76.80% 19/8/2019 9:03 72.60% 19/8/2019 9:04 78.90% 19/8/2019 9:05 74.20% 19/8/2019 9:06 76.90% 19/8/2019 9:07 75.80% 19/8/2019 9:08 82.40% 19/8/2019 9:09 60.70% 19/8/2019 9:10 55.90% 19/8/2019 9:11 46.10% 19/8/2019 9:12 43.60% 19/8/2019 9:13 60.40% 19/8/2019 9:14 68.10% 19/8/2019 9:15 75.60% 19/8/2019 9:16 120.20% 19/8/2019 9:17 218.30% 19/8/2019 9:18 73.70% 19/8/2019 9:19 65.90% 19/8/2019 9:20 68.00% 19/8/2019 9:21 77.40% 19/8/2019 9:22 77.00% 19/8/2019 9:23 76.10% 19/8/2019 9:24 64.60% 19/8/2019 9:25 64.50% 19/8/2019 9:26 73.00% 19/8/2019 9:27 78.10% 19/8/2019 9:28 74.60% 19/8/2019 9:29 85.60% 19/8/2019 9:30 76.50% 19/8/2019 9:31 72.40% 19/8/2019 9:32 69.60% 19/8/2019 9:33 61.20% 19/8/2019 9:34 59.10% 19/8/2019 9:35 46.20% 19/8/2019 9:36 52.90% 19/8/2019 9:37 87.00% 19/8/2019 9:38 76.00% 19/8/2019 9:39 73.50% 19/8/2019 9:40 73.20% 19/8/2019 9:41 72.70% 19/8/2019 9:42 74.60% 19/8/2019 9:43 68.70% 19/8/2019 9:44 68.40% 19/8/2019 9:45 65.30% 19/8/2019 9:46 85.10% 19/8/2019 9:47 103.00% 19/8/2019 9:48 110.00% 19/8/2019 9:49 87.60% 19/8/2019 9:50 58.70% 19/8/2019 9:51 47.70% 19/8/2019 9:52 69.20% 19/8/2019 9:53 74.80% 19/8/2019 9:54 69.80% 19/8/2019 9:55 63.70% 19/8/2019 9:56 63.30% 19/8/2019 9:57 72.40% 19/8/2019 9:58 71.50% 19/8/2019 9:59 71.10% 19/8/2019 10:00 80.00% 19/8/2019 10:01 108.20% 19/8/2019 10:02 63.20% 19/8/2019 10:03 45.10% 19/8/2019 10:04 46.10% 19/8/2019 10:05 47.90% 19/8/2019 10:06 76.80% 19/8/2019 10:09 287.40% 19/8/2019 10:10 62.30% 19/8/2019 10:11 66.00% 19/8/2019 10:12 70.00% 19/8/2019 10:13 68.00% 19/8/2019 10:14 73.20% 19/8/2019 10:15 73.70% 19/8/2019 10:16 73.50% 19/8/2019 10:17 71.80% 19/8/2019 10:18 67.10% 19/8/2019 10:19 71.50% 19/8/2019 10:20 65.10% 19/8/2019 10:21 66.20% 19/8/2019 10:22 61.40% 19/8/2019 10:23 101.60% 19/8/2019 10:24 188.70% 19/8/2019 10:25 160.60% 19/8/2019 10:26 82.70% 19/8/2019 10:27 65.70% 19/8/2019 10:28 65.60% 19/8/2019 10:29 68.20% 19/8/2019 10:30 65.10% 19/8/2019 10:31 47.60% 19/8/2019 10:32 54.70% 19/8/2019 10:33 66.70% 19/8/2019 10:34 55.80% 19/8/2019 10:35 82.40% 19/8/2019 10:36 73.80% 19/8/2019 10:37 161.30% 19/8/2019 10:38 139.90% 19/8/2019 10:39 151.30% 19/8/2019 10:40 176.90% 19/8/2019 10:41 120.40% 19/8/2019 10:42 64.90% 19/8/2019 10:43 60.80% 19/8/2019 10:44 54.00% 19/8/2019 10:45 56.70% 19/8/2019 10:46 76.10% 19/8/2019 10:47 68.30% 19/8/2019 10:48 70.80% 19/8/2019 10:49 71.70% 19/8/2019 10:50 65.20% 19/8/2019 10:51 51.50% 19/8/2019 10:52 63.70% 19/8/2019 10:53 76.00% 19/8/2019 10:54 69.80% 19/8/2019 10:55 70.30% 19/8/2019 10:56 70.20% 19/8/2019 10:57 70.50% 19/8/2019 10:58 66.30% 19/8/2019 10:59 70.70% 19/8/2019 11:00 68.60% 19/8/2019 11:01 69.40% 19/8/2019 11:02 74.30% 19/8/2019 11:03 71.20% 19/8/2019 11:04 73.40% 19/8/2019 11:05 69.70% 19/8/2019 11:06 55.40% 19/8/2019 11:07 50.50% 19/8/2019 11:08 59.10% 19/8/2019 11:09 68.50% 19/8/2019 11:10 67.20% 19/8/2019 11:11 64.40% 19/8/2019 11:12 65.90% 19/8/2019 11:13 69.20% 19/8/2019 11:14 69.90% 19/8/2019 11:15 69.00% 19/8/2019 11:16 66.10% 19/8/2019 11:17 61.30% 19/8/2019 11:18 68.70% 19/8/2019 11:19 72.30% 19/8/2019 11:20 57.00% 19/8/2019 11:21 63.70% 19/8/2019 11:22 73.10% 19/8/2019 11:23 58.70% 19/8/2019 11:24 60.90% 19/8/2019 11:25 77.40% 19/8/2019 11:26 81.20% 19/8/2019 11:27 61.90% 19/8/2019 11:28 65.60% 19/8/2019 11:29 59.00% 19/8/2019 11:30 57.90% 19/8/2019 11:31 62.10% 19/8/2019 11:32 65.20% 19/8/2019 11:33 71.40% 19/8/2019 11:34 62.10% 19/8/2019 11:35 85.50% 19/8/2019 11:36 151.80% 19/8/2019 11:37 81.80% 19/8/2019 11:38 65.80% 19/8/2019 11:39 43.30% 19/8/2019 11:40 70.40% 19/8/2019 11:41 109.40% 19/8/2019 11:42 98.20% 19/8/2019 11:43 61.90% 19/8/2019 11:44 78.20% 19/8/2019 11:45 198.20% 19/8/2019 11:46 55.30% 19/8/2019 11:47 57.80% 19/8/2019 11:48 86.20% 19/8/2019 11:49 65.10% 19/8/2019 11:50 77.50% 19/8/2019 11:51 90.20% 19/8/2019 11:52 119.20% 19/8/2019 11:53 57.20% 19/8/2019 11:54 61.20% 19/8/2019 11:55 68.70% 19/8/2019 11:56 70.70% 19/8/2019 11:57 70.30% 19/8/2019 11:58 74.50% 19/8/2019 11:59 69.40% 19/8/2019 12:00 70.00% 19/8/2019 12:01 70.20% 19/8/2019 12:02 69.80% 19/8/2019 12:03 69.50% 19/8/2019 12:04 66.50% 19/8/2019 12:05 62.80% 19/8/2019 12:06 69.30% 19/8/2019 12:07 69.30% 19/8/2019 12:08 66.30% 19/8/2019 12:09 64.50% 19/8/2019 12:10 66.80% 19/8/2019 12:11 51.80% 19/8/2019 12:12 66.30% 19/8/2019 12:13 68.50% 19/8/2019 12:14 60.80% 19/8/2019 12:15 46.60% 19/8/2019 12:16 55.90% 19/8/2019 12:17 51.70% 19/8/2019 12:18 75.00% 19/8/2019 12:19 85.90% 19/8/2019 12:20 137.20% 19/8/2019 12:21 123.30% 19/8/2019 12:22 56.00% 19/8/2019 12:23 52.60% 19/8/2019 12:24 64.10% 19/8/2019 12:25 39.90% 19/8/2019 12:26 66.40% 19/8/2019 12:27 66.10% 19/8/2019 12:28 65.50% 19/8/2019 12:29 42.00% 19/8/2019 12:30 46.40% 19/8/2019 12:31 52.10% 19/8/2019 12:32 64.90% 19/8/2019 12:33 52.10% 19/8/2019 12:34 69.20% 19/8/2019 12:35 66.30% 19/8/2019 12:36 62.50% 19/8/2019 12:37 63.60% 19/8/2019 12:38 58.10% 19/8/2019 12:39 89.80% 19/8/2019 12:40 73.10% 19/8/2019 12:41 53.00% 19/8/2019 12:42 60.90% 19/8/2019 12:43 78.70% 19/8/2019 12:44 129.00% 19/8/2019 12:45 72.50% 19/8/2019 12:46 124.10% 19/8/2019 12:47 115.00% 19/8/2019 12:48 146.80% 19/8/2019 12:49 92.90% 19/8/2019 12:50 69.60% 19/8/2019 12:51 49.40% 19/8/2019 12:52 45.60% 19/8/2019 12:53 76.20% 19/8/2019 12:54 60.80% 19/8/2019 12:55 47.30% 19/8/2019 12:56 49.40% 19/8/2019 12:57 80.00% 19/8/2019 12:58 59.60% 19/8/2019 12:59 52.80% 19/8/2019 13:00 42.20% 19/8/2019 13:01 48.70% 19/8/2019 13:02 45.40% 19/8/2019 13:03 49.20% 19/8/2019 13:04 72.40% 19/8/2019 13:05 59.80% 19/8/2019 13:06 72.70% 19/8/2019 13:07 99.70% 19/8/2019 13:08 92.30% 19/8/2019 13:09 41.70% 19/8/2019 13:10 50.70% 19/8/2019 13:11 50.50% 19/8/2019 13:12 68.50% 19/8/2019 13:13 109.50% 19/8/2019 13:14 110.50% 19/8/2019 13:15 75.80% 19/8/2019 13:16 63.10% 19/8/2019 13:17 65.70% 19/8/2019 13:18 60.30% 19/8/2019 13:19 65.00% 19/8/2019 13:20 52.00% 19/8/2019 13:21 54.50% 19/8/2019 13:22 53.70% 19/8/2019 13:23 67.30% 19/8/2019 13:24 62.60% 19/8/2019 13:25 64.80% 19/8/2019 13:26 79.30% 19/8/2019 13:27 50.90% 19/8/2019 13:28 64.90% 19/8/2019 13:29 56.40% 19/8/2019 13:30 48.20% 19/8/2019 13:31 59.20%
I'm not too familiar with Direct Query or its limitations. If you want a measure, I guess you could place
Table1[Timestamp] in the x axis of a chart and modify the previous code to use as measure.
Measure = VAR MinsInWindow_ = 15 //Change window lenght as required (in minutes) VAR HigherEnd_ = SELECTEDVALUE(Table1[Time Stamp]) //Read the current time. That's the higher end of the window for the averaging. VAR LowerEnd_ = HigherEnd_ - MinsInWindow_ / ( 24 * 60 ) // Determine the lower end of the window 1/(24*60) represents one minute)
RETURN CALCULATE ( //Calculate the average in the 15 min window AVERAGE ( Table1[PR] ), Table1[Time Stamp] <= HigherEnd_, Table1[Time Stamp] > LowerEnd_, ALL ( Table1 ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
83 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |