The iif function can most likely be used. Would something similar to the following give the desired results?
iif(attr([Order Date])<=today() and attr([Order Date])>=dateadd('day', -7, today()) , sum(Profit)/7, null)
I'm not sure how you'd like to do hours, but this could be further included in the calculation by referencing the specific hour(s).
Hope this helps a little bit!