Forum Discussion

heuristik's avatar
heuristik
Regular Visitor
10 years ago
Solved

Moving Average in Seconds

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:

DataTimestampDeviceNameVp
5/22/2016 0:00:00NSB-00353.1021
5/22/2016 0:00:01NSB-00353.0524
5/22/2016 0:00:02NSB-00353.127
5/22/2016 0:00:03NSB-00353.1021
5/22/2016 0:00:04NSB-00353.0773
5/22/2016 0:00:05NSB-00353.0524
5/22/2016 0:00:06NSB-00352.9779
5/22/2016 0:00:07NSB-00353.1021
5/22/2016 0:00:08NSB-00353.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.

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    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's avatar
    heuristik
    10 years ago

    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. 

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    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's avatar
      heuristik
      Regular Visitor
      10 years ago

      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.