cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

 

data.PNG

 

  

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
Solution Sage

Re: Moving average over period of hours

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

6.PNG

 

Thanks,
Xi Jin.

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
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.

 

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

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.NameDate/TimeTemperatureRelative HumidityYearMonth NameQuarterTimeDate
SensorG3.xlsx10/02/2017 22:302147.92017October422:30:0002-Oct-17
SensorG3.xlsx10/02/2017 22:452148.52017October422:45:0002-Oct-17
SensorG3.xlsx10/02/2017 23:002148.62017October423:00:0002-Oct-17
SensorG3.xlsx10/02/2017 23:152147.82017October423:15:0002-Oct-17
SensorG3.xlsx10/02/2017 23:302148.62017October423:30:0002-Oct-17
SensorG3.xlsx10/02/2017 23:452148.52017October423:45:0002-Oct-17
SensorG3.xlsx10/03/2017 00:002148.42017October400:00:0003-Oct-17
SensorG3.xlsx10/03/2017 00:152148.42017October400:15:0003-Oct-17
SensorG3.xlsx10/03/2017 00:302148.42017October400:30:0003-Oct-17
SensorG3.xlsx10/07/2017 16:452157.72017October416:45:0007-Oct-17
SensorG3.xlsx10/07/2017 17:002155.52017October417:00:0007-Oct-17
SensorG3.xlsx10/07/2017 17:152152.82017October417:15:0007-Oct-17
SensorG3.xlsx10/07/2017 17:302150.72017October417:30:0007-Oct-17
SensorG3.xlsx10/07/2017 17:452149.92017October417:45:0007-Oct-17
SensorG3.xlsx10/07/2017 18:002149.32017October418:00:0007-Oct-17
SensorG3.xlsx10/07/2017 18:152151.42017October418:15:0007-Oct-17
SensorG3.xlsx10/07/2017 18:302154.62017October418:30:0007-Oct-17
SensorG3.xlsx10/07/2017 18:452154.22017October418:45:0007-Oct-17
SensorG3.xlsx10/07/2017 19:002154.72017October419:00:0007-Oct-17
SensorG3.xlsx10/07/2017 19:1521552017October419:15:0007-Oct-17
SensorG3.xlsx10/07/2017 20:302154.72017October420:30:0007-Oct-17
SensorG3.xlsx10/07/2017 20:452154.32017October420:45:0007-Oct-17
SensorG3.xlsx10/07/2017 21:002153.42017October421:00:0007-Oct-17
SensorG3.xlsx10/07/2017 21:152152.92017October421:15:0007-Oct-17
         
Highlighted
Solution Sage
Solution Sage

Re: Moving average over period of hours

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.

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.NameDate/TimeTemperatureRelative HumidityYearMonth NameQuarterTimeDateMoving Average
SensorG3.xlsx10/02/2017 22:302147.92017October422:30:0002-Oct-17 
SensorG3.xlsx10/02/2017 22:452148.52017October422:45:0002-Oct-17 
SensorG3.xlsx10/02/2017 23:002148.62017October423:00:0002-Oct-17 
SensorG3.xlsx10/02/2017 23:152147.82017October423:15:0002-Oct-17 
SensorG3.xlsx10/02/2017 23:302148.62017October423:30:0002-Oct-17 
SensorG3.xlsx10/02/2017 23:452148.52017October423:45:0002-Oct-17 
SensorG3.xlsx10/03/2017 00:002148.42017October400:00:0003-Oct-17 
SensorG3.xlsx10/03/2017 00:152148.42017October400:15:0003-Oct-17 
SensorG3.xlsx10/03/2017 00:302148.42017October400:30:0003-Oct-1748.34
SensorG3.xlsx10/07/2017 16:452157.72017October416:45:0007-Oct-1749.43
SensorG3.xlsx10/07/2017 17:002155.52017October417:00:0007-Oct-1750.21
SensorG3.xlsx10/07/2017 17:152152.82017October417:15:0007-Oct-1750.68
SensorG3.xlsx10/07/2017 17:302150.72017October417:30:0007-Oct-1751.00
SensorG3.xlsx10/07/2017 17:452149.92017October417:45:0007-Oct-1751.14
SensorG3.xlsx10/07/2017 18:002149.32017October418:00:0007-Oct-1751.23
SensorG3.xlsx10/07/2017 18:152151.42017October418:15:0007-Oct-1751.57
SensorG3.xlsx10/07/2017 18:302154.62017October418:30:0007-Oct-1752.26
SensorG3.xlsx10/07/2017 18:452154.22017October418:45:0007-Oct-1752.90
SensorG3.xlsx10/07/2017 19:002154.72017October419:00:0007-Oct-1752.57
SensorG3.xlsx10/07/2017 19:1521552017October419:15:0007-Oct-1752.51
SensorG3.xlsx10/07/2017 20:302154.72017October420:30:0007-Oct-1752.72
SensorG3.xlsx10/07/2017 20:452154.32017October420:45:0007-Oct-1753.12
SensorG3.xlsx10/07/2017 21:002153.42017October421:00:0007-Oct-1753.51
SensorG3.xlsx10/07/2017 21:152152.92017October421:15:0007-Oct-1753.91

 

 

moving_average.PNG

Highlighted
Solution Sage
Solution Sage

Re: Moving average over period of hours

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

6.PNG

 

Thanks,
Xi Jin.

View solution in original post

Highlighted
Frequent Visitor

Re: Moving average over period of hours

Hi @v-xjiin-msft,

 

Exactly what I needed! Thank you for your time.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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