rolling average for hourly data

[baronraghu]

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

[v-shex-msft]

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

[baronraghu]

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

[baronraghu]

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

[v-shex-msft · Accepted solution]

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