Moving Average in Seconds

[heuristik]

PowerBI / DAX are well equipped for period groupings that involve days, months, quarters and years.  But, despite the description of time intelligence, there does not appear to be much (if any) functionality for time itself.  Our dataset is standard IoT log variety:

DATATIMESTAMP,DEVICEID,Vp (other values omitted) in one second intervals.  As is so often the case with these things, there is a lot of noise in the readings.  Therefore, we need to smooth out the values using averaging.

Given a set of data that looks like this:

DataTimestamp	DeviceName	Vp
5/22/2016 0:00:00	NSB-003	53.1021
5/22/2016 0:00:01	NSB-003	53.0524
5/22/2016 0:00:02	NSB-003	53.127
5/22/2016 0:00:03	NSB-003	53.1021
5/22/2016 0:00:04	NSB-003	53.0773
5/22/2016 0:00:05	NSB-003	53.0524
5/22/2016 0:00:06	NSB-003	52.9779
5/22/2016 0:00:07	NSB-003	53.1021
5/22/2016 0:00:08	NSB-003	53.0773

How can we calculate the average of now + 4 previous readings, then calculate the delta of change between this "now" average and the average from 3 seconds past?  Any help would be greatly appreciated!

Thanks.

[v-sihou-msft · Accepted solution]

@heuristik

In this scenario, because the rows are in one second intervals, we can create an index column so that we can use it to do the average calculation.

Please refer to following steps.

1. Add an index column in Query Editor.
2. Create three columns for  average of now + 4 previous readings, average from 3 seconds past and delta change.
   

   Average_of_Now+4PreReadings = 
   CALCULATE (
       CALCULATE (
           AVERAGE ( Table1[Vp] ),
           Table1[Index]
               >= VALUES ( Table1[Index] ) - 4,
           Table1[Index] <= VALUES ( Table1[Index] )
       ),
       ALLEXCEPT ( Table1, Table1[DeviceName], Table1[Index] )
   )
   

   Average_from_3SecPast = 
   CALCULATE (
       CALCULATE (
           AVERAGE ( Table1[Vp] ),
           Table1[Index]
               >= VALUES ( Table1[Index] ) - 3,
           Table1[Index] <= VALUES ( Table1[Index] )
       ),
       ALLEXCEPT ( Table1, Table1[DeviceName], Table1[Index] )
   )
   

   Delta_Change = Table1[Average_of_Now+4PreReadings] - Table1[Average_from_3SecPast]

   

Regards,

[v-sihou-msft · Accepted solution]

@heuristik

In this scenario, because the rows are in one second intervals, we can create an index column so that we can use it to do the average calculation.

Please refer to following steps.

1. Add an index column in Query Editor.
2. Create three columns for  average of now + 4 previous readings, average from 3 seconds past and delta change.
   

   Average_of_Now+4PreReadings = 
   CALCULATE (
       CALCULATE (
           AVERAGE ( Table1[Vp] ),
           Table1[Index]
               >= VALUES ( Table1[Index] ) - 4,
           Table1[Index] <= VALUES ( Table1[Index] )
       ),
       ALLEXCEPT ( Table1, Table1[DeviceName], Table1[Index] )
   )
   

   Average_from_3SecPast = 
   CALCULATE (
       CALCULATE (
           AVERAGE ( Table1[Vp] ),
           Table1[Index]
               >= VALUES ( Table1[Index] ) - 3,
           Table1[Index] <= VALUES ( Table1[Index] )
       ),
       ALLEXCEPT ( Table1, Table1[DeviceName], Table1[Index] )
   )
   

   Delta_Change = Table1[Average_of_Now+4PreReadings] - Table1[Average_from_3SecPast]

   

Regards,

[heuristik · Accepted solution]

I think you just made my week, Mr. Hou.  Testing now, but I do believe you've nailed it.  I can't thank you enough, sir.