There is a quiet elegance in the math problem this user has posed, and an even quieter frustration. They have five or six rolling 28-day totals, each overlapping the next by 21 days, and they want to isolate that single new week. The standard formula works, except for one missing piece: the value of the oldest week that fell out of their very first report. That missing number is not a minor inconvenience. It is the entire ballgame.
Here is what they need to hear: every week they calculate from here on out will carry a trace of that original guess, unless they are willing to accept a different kind of accuracy. If they assume the four weeks in their first report were equal, meaning they divide that first total by four and use that as the "oldest week," they are not solving the problem. They are choosing a starting point. That guess will ripple forward. Each subsequent week is derived from the previous one, so the error does not disappear. It decays, but it never fully flushes out. After ten reports, twenty reports, fifty reports, the influence of that first assumption becomes vanishingly small, but it is still there, mathematically present in every number they produce.
The practical question is whether that matters. For a subscription service, the difference between a truly accurate weekly number and one that is off by a fraction of a percent is often meaningless. The user is not running a physics experiment. They are making decisions about promotions, content, or pricing. If they accept the equal-split assumption and move forward, they will get a consistent, repeatable weekly series. It will be internally coherent. It just will not be perfectly anchored to reality. The only way to get a truly independent weekly number is to wait until the rolling window no longer touches that first report, but even then, the math does not reset. The chain of calculations still carries the original guess forward.
So here is the concrete takeaway: stop chasing 100 percent accuracy. It is not available to you, and it never was, given the data you have. Instead, choose a defensible assumption, document it clearly, and move on. The value in your weekly numbers is not in their absolute truth. It is in their consistency. If you are consistent, you can compare week to week, spot trends, and make better decisions than you could with no numbers at all. That is not a compromise. That is the practical reality of working with imperfect data. And it is exactly the kind of clarity that separates people who get stuck on the math from people who use it to move forward.