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...
Need Help
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?