In the picture below:
- the first column displays a “forecasted capacity” measure, imported from a Google Spreadsheet
- the second column displays a “ratio” measure, based on historical data
I would like to create a calculated measure that does: “forecasted capacity” x “ratio (moving avg.)”
Can I ‘project’ a moving average of the “ratio” measure into the future, or something equivalent?
Yes, it should be possible to project ratio in future. One option is to use some ParallelPeriod(…) to get ratio value from one of past weeks.
What would be the formula how you would like the ratio to be calculated in the future dates?
Also, please let me know, what is the ratio formula now?
Lauma / firstname.lastname@example.org
This is the formula of the ratio:
[Measures].[Story ratio] =
[Measures].[Days spent by <rep. devs> on Stories]/
[Measures].[Days spent by <rep. devs>]
The moving average of [Story ratio] might be something like:
[Measures].[Story ratio, ma.] =
So what I would like is, referring to the screenshot: the moving average value of [Story ratio] (calculated based on week 9, 8, 7, 6 and 5) is projected into W10 -> W19.
In the screenshot example W19 is the last week, but that could be any week in the future (depending on the number of rows in the Google Sheet).
Hope that clarifies things a bit.