Calulate moving average with datetime with 1 minute data

[mnascimento]

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,

[v-sihou-msft]

@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,

[Kolumam]

Hi @v-sihou-msft  @mnascimento 

Can you please explain the formula? It works but I am not able to understand properly.

Thanks

Krishna

[mnascimento]

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?

[mnascimento]

@v-sihou-msft

[v-sihou-msft]

@mnascimento

Can you share some sample data or .pbix?