Forecasting Seasonal Data using powerpivot

[Mavericks334]

Hi,

How to create a seasonal index for each month, which could be used to forecast using it in combination with historical data either with Linear or exponential forecasting.

Regards,
Renato.

 

[Mavericks334]

Sorry, it was never going to work properly without ALL() on more than just the Month Number.

On a basic level this works:

[Seasonal Index] = [Sales] / CALCULATE([Sales],ALL(Calendar))

It becomes slightly more complex if you then want to use a slicer to specify the years to use. Two questions:

- What is the purpose of the model? Is it to use this measure as part of a wider PowerPivot based tool or is it purely to derive the seasonal index?
- Is the slicer part of the calendar table?

Hi Jacob,

I still get the seasonal index as 1 for all the months.

=CALCULATE([Value_Corrected],FILTER(ALL(Year[Year]),Year[Year]<VALUES(Year[Year])))/CALCULATE([Value_Corrected],FILTER(ALL(Year),Year[Year]<VALUES(Year[Year])))

Measure that i am using.

 

[Jacob Barnett]

Think your last post got cut off.....

The stuff I've sent should work as I've tested it. The only way to make progress if for you to send me some kind of sample workbook, I've PM'd you my email.

Jacob

 

[Mavericks334]

Think your last post got cut off.....

The stuff I've sent should work as I've tested it. The only way to make progress if for you to send me some kind of sample workbook, I've PM'd you my email.

Jacob

=CALCULATE([Value_Corrected],FILTER(ALL(Year[Year]),Year[Year]<VALUES(Year[Year])))/CALCULATE([Value_Corrected],FILTER(ALL(Year),Year[Year]<VALUES(Year[Year])))
The above measure is what i have used. Unfortunately i cannot send the file.

When i create two seperate measures and check the values they are identical. [Value_corrected] is a measure that sums up all the data.

Year is the table where i have the years listed from 2008 and also i am using the same data to create a slicer

 

[theresabetterway]

This will do it, has to have something like month number in the rows or columns and needs to be filtered by a 'Year'

=CALCULATE([Sales],
FILTER(ALL(Calendar[Calendar Year]),Calendar[Calendar Year]<values(calendar[calendar year])))
/
CALCULATE([Sales],
FILTER(ALL(Calendar),Calendar[Calendar Year]<values(calendar[calendar year])))


Great thread.

</values(calendar[calendar></values(calendar[calendar>I'm working on something very similar, where I'm trying to forecast monthly sales by product by customer for next year, based on sales data of the last 12 months. The "allselected" function allows you to specify the historic period that forms the basis of the calculation, and doesn't require the filter to specify a particular year:

Fraction=divide([Sum of SALESORDERQTY],calculate([Sum of SALESORDERQTY],allselected(HistoricSales)),0)

You have to specify the 12 month time range in a slicer to take advantage of the "allselected" function. I use the "divide" function wherever possible as it automatically deals with division by zero. Each line in my historic sales table is related to a particular customer.

I have another calculated field where I multiply this fraction with my total expected sales quantity for next year (which I have already laboriously split out by product and month but not customer), but the trick here is to not link the master date table with the forecast period, otherwise it tries to split out the quantities out into the historical period again.

The only issue I'm then left with is rounding off decimal points to whole numbers (the products come in discrete unit quantities), which is mostly a problem for small customers.

 

[Mavericks334]

Thanks could you share a sample file for the same. Would like to see how it works.