cancel
Showing results for
Search instead for
Did you mean:
Member

## rolling average for hourly data

Hi All,

I have following data Column 1 denotes Date, Column 2 start time Column 3 End Time Column 4 Price

 01-01-2018 22:45:00 23:00:00 1999.69 01-01-2018 23:00:00 23:15:00 1999.79 01-01-2018 23:15:00 23:30:00 1899.72 01-01-2018 23:30:00 23:45:00 1894.18 01-01-2018 23:45:00 00:00:00 1749.99 02-01-2018 00:00:00 00:15:00 1999.69 02-01-2018 00:15:00 00:30:00 1999.07 02-01-2018 00:30:00 00:45:00 1801.79 02-01-2018 00:45:00 01:00:00 1791.81 02-01-2018 01:00:00 01:15:00 1954.14 02-01-2018 01:15:00 01:30:00 1801.46 02-01-2018 01:30:00 01:45:00 1800.81 02-01-2018 01:45:00 02:00:00 1749.47 02-01-2018 02:00:00 02:15:00 1793.62 02-01-2018 02:15:00 02:30:00 1780.16 02-01-2018 02:30:00 02:45:00 1698.83 02-01-2018 02:45:00 03:00:00 1698.92 02-01-2018 03:00:00 03:15:00 1780.75 02-01-2018 03:15:00 03:30:00 1787.65 02-01-2018 03:30:00 03:45:00 1999.06 02-01-2018 03:45:00 04:00:00 1999.15 02-01-2018 04:00:00 04:15:00 1801.68 02-01-2018 04:15:00 04:30:00 1999.03 02-01-2018 04:30:00 04:45:00 1801.45 02-01-2018 04:45:00 05:00:00 1999.39 02-01-2018 05:00:00 05:15:00 2180.26 02-01-2018 05:15:00 05:30:00 2318.63 02-01-2018 05:30:00 05:45:00 2437.46 02-01-2018 05:45:00 06:00:00 2402.88 02-01-2018 06:00:00 06:15:00 2679.67

I am looking to calculate the following

1) Average rolling price per 4 hour spread per month as shown below

 start time end time January 00:00:00 04:00:00 1938.8 01:00:00 05:00:00 2005
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: rolling average for hourly data

Hi @baronraghu,

You can refer to following calculate table formula to get a summarize table base on sample data table:

```Summary =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( 'Sample', MINUTE ( [StartTime] ) = 0 && SECOND ( [StartTime] ) = 0 ),
[Date],
[StartTime],
"End", CALCULATE ( MAX ( 'Sample'[EndTime] ), ALLEXCEPT ( 'Sample', 'Sample'[Date] ) )
),
"AVG", AVERAGEX (
FILTER (
ALL ( 'Sample' ),
'Sample'[Date] = EARLIER ( [Date] )
&& 'Sample'[StartTime] >= EARLIER ( [StartTime] )
&& 'Sample'[EndTime] <= EARLIER ( [End] )
),
[Value]
)
)
```

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
4 REPLIES 4
Community Support Team

## Re: rolling average for hourly data

Hi @baronraghu,

You can refer to following steps to create a table visual to display range average data.

Steps:

1. Create a calculate table with hour and range.

`HourList = SELECTCOLUMNS(GENERATESERIES(0,23,1),"Hour",[Value],"Range",INT([Value]/4)+1)`

2. Add calculated column to original table to extract hour from start time.

`StartHour = HOUR([Start])`

3. Create table visual.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: rolling average for hourly data

Hi,

I think the below table will be helpful

 01-01-2018 00:00:00 00:15:00 1999.21 01-01-2018 00:15:00 00:30:00 1899.96 01-01-2018 00:30:00 00:45:00 1899.51 01-01-2018 00:45:00 01:00:00 1899.13 01-01-2018 01:00:00 01:15:00 1819.77 01-01-2018 01:15:00 01:30:00 1819.32 01-01-2018 01:30:00 01:45:00 1800.78 01-01-2018 01:45:00 02:00:00 1749.69 01-01-2018 02:00:00 02:15:00 1749.57 01-01-2018 02:15:00 02:30:00 1749.45 01-01-2018 02:30:00 02:45:00 1749.58 01-01-2018 02:45:00 03:00:00 1749.83 01-01-2018 03:00:00 03:15:00 1749.65 01-01-2018 03:15:00 03:30:00 1749.71 01-01-2018 03:30:00 03:45:00 1749.71 01-01-2018 03:45:00 04:00:00 1999.01 02-01-2018 00:00:00 00:15:00 1999.69 02-01-2018 00:15:00 00:30:00 1999.07 02-01-2018 00:30:00 00:45:00 1801.79 02-01-2018 00:45:00 01:00:00 1791.81 02-01-2018 01:00:00 01:15:00 1954.14 02-01-2018 01:15:00 01:30:00 1801.46 02-01-2018 01:30:00 01:45:00 1800.81 02-01-2018 01:45:00 02:00:00 1749.47 02-01-2018 02:00:00 02:15:00 1793.62 02-01-2018 02:15:00 02:30:00 1780.16 02-01-2018 02:30:00 02:45:00 1698.83 02-01-2018 02:45:00 03:00:00 1698.92 02-01-2018 03:00:00 03:15:00 1780.75 02-01-2018 03:15:00 03:30:00 1787.65 02-01-2018 03:30:00 03:45:00 1999.06 02-01-2018 03:45:00 04:00:00 1999.15 03-01-2018 00:00:00 00:15:00 1999.77 03-01-2018 00:15:00 00:30:00 1999.33 03-01-2018 00:30:00 00:45:00 1899.85 03-01-2018 00:45:00 01:00:00 1899.21 03-01-2018 01:00:00 01:15:00 1801.93 03-01-2018 01:15:00 01:30:00 1801.9 03-01-2018 01:30:00 01:45:00 1801.73 03-01-2018 01:45:00 02:00:00 1801.48 03-01-2018 02:00:00 02:15:00 1780.53 03-01-2018 02:15:00 02:30:00 1700.99 03-01-2018 02:30:00 02:45:00 1700.78 03-01-2018 02:45:00 03:00:00 1700.33 03-01-2018 03:00:00 03:15:00 1698.99 03-01-2018 03:15:00 03:30:00 1801.19 03-01-2018 03:30:00 03:45:00 1780.44 03-01-2018 03:45:00 04:00:00 1800.79

What I would like is a below table sumarizing the average price as

 Date Start Time End Time Aver Price 01-01-2018 00:00:00 04:00:00 1938 01-01-2018 01:00:00 04:00:00 1863 01-01-2018 02:00:00 04:00:00 786 02-01-2018 00:00:00 04:00:00 2589 02-01-2018 01:00:00 04:00:00 1587 02-01-2018 02:00:00 04:00:00 1532 03-01-2018 00:00:00 04:00:00 1486 03-01-2018 01:00:00 04:00:00 1436 03-01-2018 02:00:00 04:00:00 1428

Highlighted
Member

## Re: rolling average for hourly data

In lieu with my previous post I have created a new bucket table like below

Adj_Start     Ad_End

 00:00:00 04:00:00 01:00:00 04:00:00 02:00:00 04:00:00

I created a dax formula like this

Measure = CALCULATE([average price per day],FILTER(Append1,Append1[Start Time]>=VALUES(bucket[Adj_Start])&&Append1[Start Time]<VALUES(bucket[Ad_End])))

This doesnt seem to work

Community Support Team

## Re: rolling average for hourly data

Hi @baronraghu,

You can refer to following calculate table formula to get a summarize table base on sample data table:

```Summary =
ADDCOLUMNS (
SUMMARIZE (
FILTER ( 'Sample', MINUTE ( [StartTime] ) = 0 && SECOND ( [StartTime] ) = 0 ),
[Date],
[StartTime],
"End", CALCULATE ( MAX ( 'Sample'[EndTime] ), ALLEXCEPT ( 'Sample', 'Sample'[Date] ) )
),
"AVG", AVERAGEX (
FILTER (
ALL ( 'Sample' ),
'Sample'[Date] = EARLIER ( [Date] )
&& 'Sample'[StartTime] >= EARLIER ( [StartTime] )
&& 'Sample'[EndTime] <= EARLIER ( [End] )
),
[Value]
)
)
```

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |