Comparing filtered values with a monthly average

[nicolo_blotto]

Hello,

I'm trying to create a dashboard to check how daily KPIs are performing, and I want to check their values against the average of a longer period.

I'm using a pivot table so structured:

On the first column there is the ID of the cost center I want to check. On the 5 columns I'm showing the daily value for that cost center. As you can see, right now I'm able to show for each line the values in different colours based on the average of the 5 values shown, but this is not what I'm asked from the management.

For each row, I should show the colours based on the comparison with the average value of that row on the last 1 or 3 months, to show if each day is currently under or overperforming compared to this longer average. 

I've got no idea how to tackle this problem. As a side question, I would also like to understand if there is a way to automatically select always the last 7 days in the graph as I open the app.

Thanks and regards,

Nicolò