cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Moving average over period of hours

Hi all,

I've been struggling with this one for a while and have looked at a number of similar posts but have had no success.

I want to calculate a moving average for a sensor reading of relative humidity over a period of two hours. My data is in the following format:

I have 8 different sensors so would need to do a FILTER by either Source.Name or Sensor ID. I've been trying to use AVERAGEX and various FILTERs but can't get it working.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage

## Re: Moving average over period of hours

To achieve your requirement, you can refer to following method:

First create a calculated column to make a sequence column.

```Rank =
RANKX (
FILTER (
RunningAverage,
RunningAverage[Source.Name] = EARLIER ( RunningAverage[Source.Name] )
),
RunningAverage[Date/Time],
,
ASC,
DENSE
)```

Then a new measure with AVERAGEX().

```Running Average =
IF (
MAX ( [Rank] ) > 8,
AVERAGEX (
FILTER (
ALLSELECTED ( RunningAverage ),
RunningAverage[Source.Name] = MAX ( RunningAverage[Source.Name] )
&& RunningAverage[Rank] <= MAX ( RunningAverage[Rank] )
&& RunningAverage[Rank]
>= MAX ( RunningAverage[Rank] ) - 8
),
[Relative Humidity]
)
)```

Thanks,
Xi Jin.

6 REPLIES 6
Highlighted
Super User IV

## Re: Moving average over period of hours

If you post that data as text that I can copy I can probably get you an answer. But, the answer in general probably involves using HOUR to grab the hour, subtracting two, reconstructing a datetime variable of minus 2 hours essentially and a filter that takes the MAX or some other aggregation of the current context and includes anything >= the caculated value but below the current value. But, it's all just words unless you provide the data because I'm not typing all that in manually.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Moving average over period of hours

Hi Greg,

Thanks for your response. Please see below for a sample of the data. The method you describe sounds like what I need and have thus far failed to implement. I am new to PowerBI and DAX and can't seem to get this working.

 Source.Name Date/Time Temperature Relative Humidity Year Month Name Quarter Time Date SensorG3.xlsx 10/02/2017 22:30 21 47.9 2017 October 4 22:30:00 02-Oct-17 SensorG3.xlsx 10/02/2017 22:45 21 48.5 2017 October 4 22:45:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:00 21 48.6 2017 October 4 23:00:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:15 21 47.8 2017 October 4 23:15:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:30 21 48.6 2017 October 4 23:30:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:45 21 48.5 2017 October 4 23:45:00 02-Oct-17 SensorG3.xlsx 10/03/2017 00:00 21 48.4 2017 October 4 00:00:00 03-Oct-17 SensorG3.xlsx 10/03/2017 00:15 21 48.4 2017 October 4 00:15:00 03-Oct-17 SensorG3.xlsx 10/03/2017 00:30 21 48.4 2017 October 4 00:30:00 03-Oct-17 SensorG3.xlsx 10/07/2017 16:45 21 57.7 2017 October 4 16:45:00 07-Oct-17 SensorG3.xlsx 10/07/2017 17:00 21 55.5 2017 October 4 17:00:00 07-Oct-17 SensorG3.xlsx 10/07/2017 17:15 21 52.8 2017 October 4 17:15:00 07-Oct-17 SensorG3.xlsx 10/07/2017 17:30 21 50.7 2017 October 4 17:30:00 07-Oct-17 SensorG3.xlsx 10/07/2017 17:45 21 49.9 2017 October 4 17:45:00 07-Oct-17 SensorG3.xlsx 10/07/2017 18:00 21 49.3 2017 October 4 18:00:00 07-Oct-17 SensorG3.xlsx 10/07/2017 18:15 21 51.4 2017 October 4 18:15:00 07-Oct-17 SensorG3.xlsx 10/07/2017 18:30 21 54.6 2017 October 4 18:30:00 07-Oct-17 SensorG3.xlsx 10/07/2017 18:45 21 54.2 2017 October 4 18:45:00 07-Oct-17 SensorG3.xlsx 10/07/2017 19:00 21 54.7 2017 October 4 19:00:00 07-Oct-17 SensorG3.xlsx 10/07/2017 19:15 21 55 2017 October 4 19:15:00 07-Oct-17 SensorG3.xlsx 10/07/2017 20:30 21 54.7 2017 October 4 20:30:00 07-Oct-17 SensorG3.xlsx 10/07/2017 20:45 21 54.3 2017 October 4 20:45:00 07-Oct-17 SensorG3.xlsx 10/07/2017 21:00 21 53.4 2017 October 4 21:00:00 07-Oct-17 SensorG3.xlsx 10/07/2017 21:15 21 52.9 2017 October 4 21:15:00 07-Oct-17
Highlighted
Solution Sage

## Re: Moving average over period of hours

Could you please share us your expected result based on this sample data? So that we'll get a right direction.

Thanks,
Xi Jin.

Highlighted
Frequent Visitor

## Re: Moving average over period of hours

Hi @v-xjiin-msft,

Of course. So if I was looking for a moving average of 2 hours, given a 15min interval between readings, I would want to sum the current value with the 8 previous readings for that sensor and divide by 9.

Having done this with the sample data above it has made me think about how to deal with missing data. In general the dataset is relatively complete, however as you can see there are missing data. Not sure if I would first need to add in missing rows and then carry out a linear interpolation, or if the measure to calculate moving average could deal with missing data.

 Source.Name Date/Time Temperature Relative Humidity Year Month Name Quarter Time Date Moving Average SensorG3.xlsx 10/02/2017 22:30 21 47.9 2017 October 4 22:30:00 02-Oct-17 SensorG3.xlsx 10/02/2017 22:45 21 48.5 2017 October 4 22:45:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:00 21 48.6 2017 October 4 23:00:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:15 21 47.8 2017 October 4 23:15:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:30 21 48.6 2017 October 4 23:30:00 02-Oct-17 SensorG3.xlsx 10/02/2017 23:45 21 48.5 2017 October 4 23:45:00 02-Oct-17 SensorG3.xlsx 10/03/2017 00:00 21 48.4 2017 October 4 00:00:00 03-Oct-17 SensorG3.xlsx 10/03/2017 00:15 21 48.4 2017 October 4 00:15:00 03-Oct-17 SensorG3.xlsx 10/03/2017 00:30 21 48.4 2017 October 4 00:30:00 03-Oct-17 48.34 SensorG3.xlsx 10/07/2017 16:45 21 57.7 2017 October 4 16:45:00 07-Oct-17 49.43 SensorG3.xlsx 10/07/2017 17:00 21 55.5 2017 October 4 17:00:00 07-Oct-17 50.21 SensorG3.xlsx 10/07/2017 17:15 21 52.8 2017 October 4 17:15:00 07-Oct-17 50.68 SensorG3.xlsx 10/07/2017 17:30 21 50.7 2017 October 4 17:30:00 07-Oct-17 51.00 SensorG3.xlsx 10/07/2017 17:45 21 49.9 2017 October 4 17:45:00 07-Oct-17 51.14 SensorG3.xlsx 10/07/2017 18:00 21 49.3 2017 October 4 18:00:00 07-Oct-17 51.23 SensorG3.xlsx 10/07/2017 18:15 21 51.4 2017 October 4 18:15:00 07-Oct-17 51.57 SensorG3.xlsx 10/07/2017 18:30 21 54.6 2017 October 4 18:30:00 07-Oct-17 52.26 SensorG3.xlsx 10/07/2017 18:45 21 54.2 2017 October 4 18:45:00 07-Oct-17 52.90 SensorG3.xlsx 10/07/2017 19:00 21 54.7 2017 October 4 19:00:00 07-Oct-17 52.57 SensorG3.xlsx 10/07/2017 19:15 21 55 2017 October 4 19:15:00 07-Oct-17 52.51 SensorG3.xlsx 10/07/2017 20:30 21 54.7 2017 October 4 20:30:00 07-Oct-17 52.72 SensorG3.xlsx 10/07/2017 20:45 21 54.3 2017 October 4 20:45:00 07-Oct-17 53.12 SensorG3.xlsx 10/07/2017 21:00 21 53.4 2017 October 4 21:00:00 07-Oct-17 53.51 SensorG3.xlsx 10/07/2017 21:15 21 52.9 2017 October 4 21:15:00 07-Oct-17 53.91

Highlighted
Solution Sage

## Re: Moving average over period of hours

To achieve your requirement, you can refer to following method:

First create a calculated column to make a sequence column.

```Rank =
RANKX (
FILTER (
RunningAverage,
RunningAverage[Source.Name] = EARLIER ( RunningAverage[Source.Name] )
),
RunningAverage[Date/Time],
,
ASC,
DENSE
)```

Then a new measure with AVERAGEX().

```Running Average =
IF (
MAX ( [Rank] ) > 8,
AVERAGEX (
FILTER (
ALLSELECTED ( RunningAverage ),
RunningAverage[Source.Name] = MAX ( RunningAverage[Source.Name] )
&& RunningAverage[Rank] <= MAX ( RunningAverage[Rank] )
&& RunningAverage[Rank]
>= MAX ( RunningAverage[Rank] ) - 8
),
[Relative Humidity]
)
)```

Thanks,
Xi Jin.

Highlighted
Frequent Visitor

## Re: Moving average over period of hours

Exactly what I needed! Thank you for your time.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors