Best Of

Would be good to highlight that if you go the ETL route, things will go eerie if besides the selection of # of days, the users (or you) need to apply apply filters to the card level, as the Rank and Window Tiles won't be responsive to these filters and might end up throwing odd results (One of the key difference on when to use a ETL vs BeastMode).

Thinking a bit more on this, if creating a new Dataset is not a big problem for you, I think I got a solution:

Now let's deep dive on how this works:
You have a dataset that looks similar to your Google Sheet, a set of dates with a single value for each date:

You create a ETL that self joins the dataset into itself and then filters to keep only the previous dates next to the current one:

The to Join operations just add a 1 to each to do a product of multiplying the table by itself with the Join Data.

This returns way more rows than what we need, so now that we've renamed to old values as Preexisting, we filter those out prior to storing to the new Dataset. Since in your current example, you're not using the current date value as part of the Average calculation, we check that the Date needs to be higher than the preexisting one, and that the Preexisting one is greater than the Date moved 365 days back (since you mentioned this is the limit on how far back users will be able to update the range, if you want to allow to go even further back just change this to the right value).

With this you now have a Dataset that contains as many entries for each Day as there were entries that matched to be in the look back period. Something like:

From here, it is easy to build a card that will have a calculated field that leverages the Variable we created (`Days Threshold` in my case), to determine which values should be included as part of the average:

And then you just use it in your card as shown on the first screenshot.

This was a good thinking exercise prior to the weekend :)

PD. Keep in mind that the way the BeastMode was written is to simulate the function you had in Sheets, in which missing days won't affect the Average calculation and it'll be made only using the available data points, should you want to change this behavior, you would divide by the Variable instead. Also this will only show dates for which you have data, you'd need to adjust the ETL to fill in missing dates between the ToJoinA and Join Data Operations, this to prevent adding dates with no values as preexisting entries which would in turn affect the Beast Mode.

You could use the group by tile in MagicETL to create a new column for the most recent completed month (or last 30 days, or whatever period you want to use). Basically you would use the rank/window function to identify the top 5, then calculate their total in the period as a column, calculate the total for the entire period, and then use the Formula tile to divide those 2 columns to generate your % column. You can then use this column inside of Analyzer or in a Beast Mode.