HELP! MTD/YTD Rolling Budgets Formulas

[Unblvbl]

I've run into a couple of "challenges" that are stumping me.
Any help, insight, suggestions that may be provided are very much appreciated.

I need formulas that will automatically update a MTD budget.

Customer 1 - Yearly budget of 100
2 - Yearly budget of 75
3 - Yearly budget of 50.

The MTD budget is static two out of the three months of each quarter but the last month in each quarter it changes due to the # of weeks (5 instead of 4).
So, in Excel the MTD for customer 1 is 100*(0.24)*4/13.
100 is the budget, the 0.24 is the quarterly breakdown (changes every quarter), 4 is the number of weeks in that particular month and 13 is the total number of weeks in the quarter.
I'd like the formula to update the budget for each quarter (i.e. changing from 0.24 to 0.25 for the new quarter and then .25 to .26 for Q4) as well as change from 4 to 5 when it is the last month of each quarter.

I have a similar question on YTD budgets but the formula is far more complex and will hold off on asking it for now.

Thank you.

 

[blom0344]

Depending on your exact requirements you may be able to solve this with an additional file (=Dataprovider) that holds the calculating parameters (=measures).

It will depend on whether have access to the universe/underlying database or that you have just access to the reporting tool. Which database are you working on?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Unblvbl]

I do not have access to change the underlying database (Universe), only the reporting tool.
How would I solve this with another Dataprovider?

Thank you for your help.