Pivot Table average in stead of Grand Total

If you add a "Category" helper-column to your data, you can get the results you want.

Based on the date, the Category column formula would return:
Curr
AvgRng
Other

Then your pivot table can use that Category in the column section and average the values.
Since the Curr category would only have one date, the average would be the total.
The AvgRng would have 5 dates and calculate their average.
Hide the columns you don't need.

Is that something you can work with?

Hello Wilem87 and Welcome to Excel Forum.
There may be more to this than I am considering, however I have attempted to construct the a table raw data that might feed a pivot table such as the sample shown in the file. Note that the table was produced using Power Query which is a standard feature in the 2016 version of Excel. The dates in the resulting Pivot Table can be changed using Date filters (cell F2) > Between. I you choose the dates 2/10/2019 and 3/17/2019 you'll see that average of the previous five weeks updates. The formula used for previous five weeks is the same as in the sample.
Perhaps if you could upload a desensitized sample of the raw data or Access table we could be of more help.
Let us know if you have any questions.

You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.