| Downloads |
|---|
| 23835.zip |
A good analysis solution provides up-to-date, relevant information. For some businesses, the most important information predicts end-of-quarter sales figures. Using the FoodMart 2000 Sales cube, create a calculated measure that returns a prediction of unit sales for the end of the current quarter. Then, use this calculated measure in an MDX query to predict the unit sales for each month in fourth quarter 1997.
To set up your prediction, figure out how far along you are in the current quarter—for example, you might be in the first month of fourth quarter 1997. Then, look back at the first three quarters of 1997 and determine what percentage of the total quarterly sales were complete after the first month of each quarter. Average the percentages from the previous three quarters, and use that number to predict what your sales will be when the fourth quarter is complete.
I answered this question by first creating the three calculated members that Listing A shows: PercentComplete1, PercentComplete2, and PercentComplete3. These calculated members determine for each of the past three quarters what percentage of the total quarterly sales was complete by the current month. The measure AvgPercComplete then averages these three percentages to estimate what percentage of sales is complete in the current quarter. Predicted Sales uses AvgPercComplete to predict the sales for the current quarter.
The query in Listing A displays the current quarter's to-date sales, an estimate of what percentage of the total sales for this quarter have been achieved, and a prediction of total sales for the current quarter. The query displays these three numbers for each of the months in fourth quarter 1997 so that you can see what the predictions were at the end of each of these months.
