cancel
Showing results for
Did you mean:
Member

## Calculate 15 min Moving Average for 1 min data

Hi All,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: Calculate 15 min Moving Average for 1 min data

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

3 REPLIES 3
Super User III

## Re: Calculate 15 min Moving Average for 1 min data

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

Member

## Re: Calculate 15 min Moving Average for 1 min data

Hi @AlB

```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%```
Super User III

## Re: Calculate 15 min Moving Average for 1 min data

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!