Moving average over period of hours

[josh_sheffield]

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.

[Greg_Deckler]

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.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[josh_sheffield]

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

[v-xjiin-msft]

Hi @josh_sheffield,

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

Thanks,
Xi Jin.

[josh_sheffield]

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

[v-xjiin-msft · Accepted solution]

Hi @josh_sheffield,

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.

[Anonymous]

Hello @v-xjiin-msft I am trying to use your example in my work however i have a question. In the rank column you appear to have two datetime columns, Source.Name and Date/Time. Is Source.Name being used as an index or something else? Why do i need both?

I have a similar problem, 2 columns. A datetime and a value and i want to find the rolling average of the value column over the last x hours. 

How would you suggest to manage a non-constant time step. I see in the example above you look back at the last 8 intervals, each interval being 15 minutes. My data set has an uneven logging period. 

Many thanks

Will

[Anonymous]

Hello

I am expanding on this since my problem is not exactly the same. I have a dataset for a crane operation and labels of active / not active for each row. Each row varys in duration from 5 seconds to 1 minute (and could change in the future). For any row where activity = 0 i want to averge backwards over the previous x hours/mins as well as forward over the same period. If the activity = 0 then i will call it parked if > 0 then call it not parked. 

I couldnt even do this in excel hence the table below is incomplete. Hopefully my explaination is clear enough. I think i need this as a column rather than a measure since i want to use it as a filter to remove all non-relevant rows. I did manage to complete this as a measure by grouping the time into hour bins however this is not so useful in filters where i want to remove specific rows. 

Many thanks for your help in advance. 

Will

[josh_sheffield]

Hi @v-xjiin-msft,

Exactly what I needed! Thank you for your time.