cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mnascimento
New Member

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,

5 REPLIES 5
Microsoft
Microsoft

Re: Calulate moving average with datetime with 1 minute data

@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
New Member

Re: Calulate moving average with datetime with 1 minute data

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
New Member

Re: Calulate moving average with datetime with 1 minute data

Microsoft
Microsoft

Re: Calulate moving average with datetime with 1 minute data

@mnascimento

 

Can you share some sample data or .pbix?

Kolumam Post Partisan
Post Partisan

Re: Calulate moving average with datetime with 1 minute data

Hi @v-sihou-msft  @mnascimento 

 

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

 

Thanks

Krishna

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors