Showing results for 
Search instead for 
Did you mean: 
Jkilb Regular Visitor
Regular Visitor

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



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! 



NameTimeStampOil VolumeRecent Oil 10 Hour AveragePrevious Oil 10 Hour Average
DL_CLC19151262019-08-23 9:001.642112971.641414691.572186399
DL_CLC19151262019-08-23 8:001.64211297  
DL_CLC19151262019-08-23 7:001.64211297  
DL_CLC19151262019-08-23 6:001.674613953  
DL_CLC19151262019-08-23 5:001.689777017  
DL_CLC19151262019-08-23 4:001.668872952  
DL_CLC19151262019-08-23 3:001.62539506  
DL_CLC19151262019-08-23 2:001.683483005  
DL_CLC19151262019-08-23 1:001.636523962  
DL_CLC19151262019-08-23 0:001.509142041  
DL_CLC19151262019-08-22 23:001.565590024  
DL_CLC19151262019-08-22 22:001.483011007  
DL_CLC19151262019-08-22 21:001.419620991  
DL_CLC19151262019-08-22 20:001.256675959  
DL_CLC19151262019-08-22 19:001.655483007  
DL_CLC19151262019-08-22 18:001.497650027  
DL_CLC19151262019-08-22 17:002.131804943  
DL_CLC19151262019-08-22 16:001.671095014  
DL_CLC19151262019-08-22 15:001.2852  
DL_CLC19151262019-08-22 14:001.755733013  
DL_HOILE5H25_012019-08-23 9:0020.6492404918.8831629817.91227498
DL_HOILE5H25_012019-08-23 8:0020.64924049  
DL_HOILE5H25_012019-08-23 7:0020.64924049  
DL_HOILE5H25_012019-08-23 6:009.963178635  
DL_HOILE5H25_012019-08-23 5:0015.98849964  
DL_HOILE5H25_012019-08-23 4:0020.50597954  
DL_HOILE5H25_012019-08-23 3:0020.25040054  
DL_HOILE5H25_012019-08-23 2:0020.27313995  
DL_HOILE5H25_012019-08-23 1:0020.20447922  
DL_HOILE5H25_012019-08-23 0:0019.69823074  
DL_HOILE5H25_012019-08-22 23:0025.70675087  
DL_HOILE5H25_012019-08-22 22:0010.66853046  
DL_HOILE5H25_012019-08-22 21:0019.87920952  
DL_HOILE5H25_012019-08-22 20:0020.48308945  
DL_HOILE5H25_012019-08-22 19:0019.89517021  
DL_HOILE5H25_012019-08-22 18:0020.90662003  
DL_HOILE5H25_012019-08-22 17:0020.17598915  
DL_HOILE5H25_012019-08-22 16:0020.35617065  
DL_HOILE5H25_012019-08-22 15:005.226029873  
DL_HOILE5H25_012019-08-22 14:0015.82518959  


Super User
Super User

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


You may download my PBI file from here.

Hope this helps.


Ashish Mathur
Jkilb Regular Visitor
Regular Visitor

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

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!


10 Hours.PNG2 Hours.PNG



Super User
Super User

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


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

Ashish Mathur

Helpful resources

New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)