I have utilized the forecasting resources to get a great start, yet I have 1 additional condition to add.
Each Sales Improvement projects, will have a estimated annual value, so have calculated and allocated the value over 12 months, [Monthly Contribute] , however each project will have it’s own start dates, so some might start in Jan 2018 to run thru Dec 2018, some might start in August 2017 to run thru July 2018
Any suggestions on how to handle the various start/end dates conditions to get the expected Sales $ for the current and previous months.
Thanks
Probably going to have to have a look at the data setup and model you’re working with here to add any value. Just a bit difficult to imagine everything that could be going on within your model on this one.
Are you able to add images describing exactly the setup and then output you need?
I am looking to calc the YTD contribution from the GMI Table[annualized Gross Margin $ Impact] for the [sold to] . The start month and end month condition are throwing me off as there will be no consistent start month, however the contribute length will be 12 months.
Once, I get that calc, I will apply it in a similar senario as your forecasting/budget resource board.
That is why a fact table is included in the data model and then provide additional analysis on those results.
What you are probably going to have to do here is create a lookup table that can filter both your actuals and your budgets at the same time I think.
So you’ll have to create a table with just the Month and Year as the column of unique values.
You need this because you want the filtering to just work here so your formula don’t have to be too complex.
That way you should be able to select a month and year and have the filters flow down the ‘waterfall’ as I say, hit the date table, which flows through to the fact table, while at the same time hitting the budget table with the specific dates you’re working with.
It’s not super easy but this is the idea.
Check out this video here
Especially take note of where the ‘Short Month’ lookup table is and how it connects into the model. What you require here is a very similar concept to this scenario.
