Forum Discussion

mnascimento's avatar
mnascimento
New Member
9 years ago

Calulate moving average with datetime with 1 minute data

Hi,

 

I have a data set where my datetime is for every 1 minute data and i need the moving average values for 15 minutes.

 

the date related filters are just for months, days and years. Any ideas on how to make this on a minute base?

 

Thanks,

Need Help

5 Replies

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

    mnascimento

     

    For your requirement, you can add an [1 min ago] column in your table like:

     

    1 min ago = Table[DateTime]-1/(24*60)

    I assume you fact data is on second level. You can create the moving average measure like below:

     

     

    1 min Moving Average =
CALCULATE (
    AVERAGE ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[DateTime] <= MAX ( 'Table'[DateTime] )
            && 'Table'[DateTime] >= MAX ( 'Table'[1 min ago] )
    )
)

    Regards,

     

    • mnascimento's avatar
      mnascimento
      New Member
      9 years ago

      Dear Simon

       

      I have added a 15 min ago column and did the same code as you but the value calculated was the same for the all the column.

       

      15min ago = Data[Date_Time]-15/(24*60) - This got the readings that i wanted.

       

      NO 15min = calculate( AVERAGE(Data[NO Ref]);filter(all(Data);Data[Date_Time]<=Max(Data[Date_Time]) && Data[Date_Time]>= Data[15min ago]))

       

      I am using values as dd/mm/yyyy hh:mm, should i use it as decimal?