Hourly Localized Average (Most recent 10 hours vs 10 hours before)

[Anonymous]

Hi, 

I know there's a lot of previous forum posts on averages however I haven't been able to find a solution for localized hourly averages. I've seen solutions for daily localized averages but not hourly averages. 

I would like to compare an average of the most recent 10 hours vs the 10 hours immediately before this. I've included sample data below. Because there's two wells in the example, I've given the solution for both wells. I don't need the solutions to show up in a column like this, I would rather use them as a measure so I can subtract them. 

Thanks in advance! 

Name	TimeStamp	Oil Volume	Recent Oil 10 Hour Average	Previous Oil 10 Hour Average
DL_CLC1915126	2019-08-23 9:00	1.64211297	1.64141469	1.572186399
DL_CLC1915126	2019-08-23 8:00	1.64211297
DL_CLC1915126	2019-08-23 7:00	1.64211297
DL_CLC1915126	2019-08-23 6:00	1.674613953
DL_CLC1915126	2019-08-23 5:00	1.689777017
DL_CLC1915126	2019-08-23 4:00	1.668872952
DL_CLC1915126	2019-08-23 3:00	1.62539506
DL_CLC1915126	2019-08-23 2:00	1.683483005
DL_CLC1915126	2019-08-23 1:00	1.636523962
DL_CLC1915126	2019-08-23 0:00	1.509142041
DL_CLC1915126	2019-08-22 23:00	1.565590024
DL_CLC1915126	2019-08-22 22:00	1.483011007
DL_CLC1915126	2019-08-22 21:00	1.419620991
DL_CLC1915126	2019-08-22 20:00	1.256675959
DL_CLC1915126	2019-08-22 19:00	1.655483007
DL_CLC1915126	2019-08-22 18:00	1.497650027
DL_CLC1915126	2019-08-22 17:00	2.131804943
DL_CLC1915126	2019-08-22 16:00	1.671095014
DL_CLC1915126	2019-08-22 15:00	1.2852
DL_CLC1915126	2019-08-22 14:00	1.755733013
DL_HOILE5H25_01	2019-08-23 9:00	20.64924049	18.88316298	17.91227498
DL_HOILE5H25_01	2019-08-23 8:00	20.64924049
DL_HOILE5H25_01	2019-08-23 7:00	20.64924049
DL_HOILE5H25_01	2019-08-23 6:00	9.963178635
DL_HOILE5H25_01	2019-08-23 5:00	15.98849964
DL_HOILE5H25_01	2019-08-23 4:00	20.50597954
DL_HOILE5H25_01	2019-08-23 3:00	20.25040054
DL_HOILE5H25_01	2019-08-23 2:00	20.27313995
DL_HOILE5H25_01	2019-08-23 1:00	20.20447922
DL_HOILE5H25_01	2019-08-23 0:00	19.69823074
DL_HOILE5H25_01	2019-08-22 23:00	25.70675087
DL_HOILE5H25_01	2019-08-22 22:00	10.66853046
DL_HOILE5H25_01	2019-08-22 21:00	19.87920952
DL_HOILE5H25_01	2019-08-22 20:00	20.48308945
DL_HOILE5H25_01	2019-08-22 19:00	19.89517021
DL_HOILE5H25_01	2019-08-22 18:00	20.90662003
DL_HOILE5H25_01	2019-08-22 17:00	20.17598915
DL_HOILE5H25_01	2019-08-22 16:00	20.35617065
DL_HOILE5H25_01	2019-08-22 15:00	5.226029873
DL_HOILE5H25_01	2019-08-22 14:00	15.82518959

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thank you for your quick response, I appreciate it! @Ashish_Mathur 

I split the date and time column by delim like you did in the file and used the measure code but I'm not getting the correct answer. It's just slightly off. 

In your measure, how does the "TIME" function know to reference the most recent "Date" column? Does this time function default to the most recent Date? If so, how does it know which date column to use? Here are some images. The first shows the code you provided. In the second image I changed the last "time" filter to 1 hour in the measure, it should equal the average of the most recent two hours correct? 

As a side note, the file has updated since I sent it so the oil volumes are slightly different.

Thanks again for your help!

[Ashish_Mathur]

Hi,

I cannot understand.  I will need to see your file.