Rolling average of last 3 samples received

[banthorpe]

I have  a table that contains data from a sensor collected every 5 minutes. I want to create a rolling average of the last 3 samples received so I can compare the current value to the average of the last 15 mins. Any suggestions please?

-Dave

[Greg_Deckler]

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

That being said, something like this might work:

Column = 
AVERAGEX(FILTER(ALL(Table2),[Time]>=EARLIER([Time])-10*.0006944444444444444),[Column1])

[banthorpe]

thanks for that - so to add some more  context:

This ia a DirectQuery table so I believe I have to use measures instead of columns (if I understand correctly)?

If I understand your suggestion correctly then this is what I should be using:

Measure = AVERAGEX(FILTER(ALL(SensorData),[timestamp]>=EARLIER([timestamp])-10*.0006944444444444444),[temperature])

but this throws the error:  EARLIER/EARLIEST refers to an earlier row context which doesn't exist.  Is this because it is acting on a measure as opposed to a column (due to it being Direct Query) ?

[Greg_Deckler · Accepted solution]

Right, turns out that details matter. 🙂

EARLIER generally won't work in a measure because, it's more of a column function. So, instead you want to do something like:

Measure = 
VAR __time = MAX([timestamp])
RETURN
AVERAGEX(FILTER(ALL(SensorData),[timestamp]>=__time-10*.0006944444444444444),[temperature])

[banthorpe]

great thanks - just to be clear, can you clarify what the calc 10*-0.000694444444 is? I'm assuming that we are removing time in fractions of a day? 

So one 5 minute sample = 1 / 24 / 12 = 0.0034722222 (5 min as a fraction of a day)

So if I wanted the last 3 samples I would look to be looking for timestamp >= 3 * 0.003472222   ???

Using this data:

timestamp                      temp

15/10/2018 12:55:49       71.70

15/10/2018 12:50:49       72.20

15/10/2018 12:45:49       72.70

15/10/2018 12:40:49       72.70

If I calculate in Excel:

the average of 12:55,12:50, 12:45 = 72.0 

the average of 12:50,12:45, 12:40 = 72.53

The output of the Measure (using 3 x  0.003472222)  = 72.95

This seems wrong as to get an average of 72.95 there would have to be data >= 73 in the dataset of those samples and there isn't.

[Greg_Deckler]

A date/datetime column in DAX is really just a decimal number. The integer portion of the number is the number of days since December 30th, 1899. The decimal portion is the time component. So, to add an hour you would add:

1/24

to add minutes

1/24/60

and to add seconds

1/24/60/60

[banthorpe]

thanks for the clarification. Is there  a way to visualize the dataset returned by that filter so I can see what it is calculating on?

[banthorpe]

Ignore last question - managed to sort it by opening new file in Desktop using Query mode and creating a new table using the filter. Enabled me to look at filter output to make sure it was grabbing the right data.

Thanks for the help