Reply
Member
Posts: 47
Registered: ‎01-11-2017

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-201822:45:0023:00:001999.69
01-01-201823:00:0023:15:001999.79
01-01-201823:15:0023:30:001899.72
01-01-201823:30:0023:45:001894.18
01-01-201823:45:0000:00:001749.99
02-01-201800:00:0000:15:001999.69
02-01-201800:15:0000:30:001999.07
02-01-201800:30:0000:45:001801.79
02-01-201800:45:0001:00:001791.81
02-01-201801:00:0001:15:001954.14
02-01-201801:15:0001:30:001801.46
02-01-201801:30:0001:45:001800.81
02-01-201801:45:0002:00:001749.47
02-01-201802:00:0002:15:001793.62
02-01-201802:15:0002:30:001780.16
02-01-201802:30:0002:45:001698.83
02-01-201802:45:0003:00:001698.92
02-01-201803:00:0003:15:001780.75
02-01-201803:15:0003:30:001787.65
02-01-201803:30:0003:45:001999.06
02-01-201803:45:0004:00:001999.15
02-01-201804:00:0004:15:001801.68
02-01-201804:15:0004:30:001999.03
02-01-201804:30:0004:45:001801.45
02-01-201804:45:0005:00:001999.39
02-01-201805:00:0005:15:002180.26
02-01-201805:15:0005:30:002318.63
02-01-201805:30:0005:45:002437.46
02-01-201805:45:0006:00:002402.88
02-01-201806:00:0006:15:002679.67


I am looking to calculate the following

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

 

start timeend timeJanuary
00:00:0004:00:001938.8
01:00:0005:00:002005
Community Support Team
Posts: 6,908
Registered: ‎08-14-2016

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.

13.PNG

 

Regards,

Xiaoxin Sheng

Member
Posts: 47
Registered: ‎01-11-2017

Re: rolling average for hourly data

Hi, 

 

I think the below table will be helpful

 

01-01-201800:00:0000:15:001999.21
01-01-201800:15:0000:30:001899.96
01-01-201800:30:0000:45:001899.51
01-01-201800:45:0001:00:001899.13
01-01-201801:00:0001:15:001819.77
01-01-201801:15:0001:30:001819.32
01-01-201801:30:0001:45:001800.78
01-01-201801:45:0002:00:001749.69
01-01-201802:00:0002:15:001749.57
01-01-201802:15:0002:30:001749.45
01-01-201802:30:0002:45:001749.58
01-01-201802:45:0003:00:001749.83
01-01-201803:00:0003:15:001749.65
01-01-201803:15:0003:30:001749.71
01-01-201803:30:0003:45:001749.71
01-01-201803:45:0004:00:001999.01
02-01-201800:00:0000:15:001999.69
02-01-201800:15:0000:30:001999.07
02-01-201800:30:0000:45:001801.79
02-01-201800:45:0001:00:001791.81
02-01-201801:00:0001:15:001954.14
02-01-201801:15:0001:30:001801.46
02-01-201801:30:0001:45:001800.81
02-01-201801:45:0002:00:001749.47
02-01-201802:00:0002:15:001793.62
02-01-201802:15:0002:30:001780.16
02-01-201802:30:0002:45:001698.83
02-01-201802:45:0003:00:001698.92
02-01-201803:00:0003:15:001780.75
02-01-201803:15:0003:30:001787.65
02-01-201803:30:0003:45:001999.06
02-01-201803:45:0004:00:001999.15
03-01-201800:00:0000:15:001999.77
03-01-201800:15:0000:30:001999.33
03-01-201800:30:0000:45:001899.85
03-01-201800:45:0001:00:001899.21
03-01-201801:00:0001:15:001801.93
03-01-201801:15:0001:30:001801.9
03-01-201801:30:0001:45:001801.73
03-01-201801:45:0002:00:001801.48
03-01-201802:00:0002:15:001780.53
03-01-201802:15:0002:30:001700.99
03-01-201802:30:0002:45:001700.78
03-01-201802:45:0003:00:001700.33
03-01-201803:00:0003:15:001698.99
03-01-201803:15:0003:30:001801.19
03-01-201803:30:0003:45:001780.44
03-01-201803:45:0004:00:001800.79

 

 

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

 

DateStart TimeEnd TimeAver Price
01-01-201800:00:0004:00:001938
01-01-201801:00:0004:00:001863
01-01-201802:00:0004:00:00786
02-01-201800:00:0004:00:002589
02-01-201801:00:0004:00:001587
02-01-201802:00:0004:00:001532
03-01-201800:00:0004:00:001486
03-01-201801:00:0004:00:001436
03-01-201802:00:0004:00:001428

 

 

Member
Posts: 47
Registered: ‎01-11-2017

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:0004:00:00
01:00:0004:00:00
02:00:0004: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