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.

 

[Jacob Barnett]

There are endless possibilities in terms of the minutiae of how you would want to perform the calculation but ALL() in CALCULATE() is the technique you are probably looking for.

On the basis that you have a measure called [Sales] and the time period month number comes from a table called calendar:

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

There are two caveats:

- You will need to put a further time restriction in your second calculate, probably to only include full completed years.

- It may behave strangely in grand totals so depending on how you intend to use it you could package it like this:

[Seasonal Index] = if ( HASONEVALUE(Calendar[Month Number]), [Sales] / CALCULATE([Sales],ALL(Calendar[Month Number])), BLANK())

On the downside that would stop your [Seasonal Index] measure 'magically' working if you decided to use it at a higher level e.g. Quarterly.

Hope this helps.
Jacob

 

[Mavericks334]

Hi Jacob,

I have tried the above suggested measures, i get 1 for all the measures, as the seasonal index.

Regards,
Renato.

 

[Mavericks334]

Forgot to create a relationship, not it works gr8

 

[Mavericks334]

The Problem, now is that if i had current fiscal years data, it takes that numbers as well. How that could be avoided. i tried to use

CALCULATE([Sales],ALL(Calendar[Month Number]),Year< [Max Year]),

Where MAx year is the year from the slicer

 

[Mavericks334]

The Seasonal index changes when i select a year in the slicer and gives me that year seasonal index, who could i see that the slicer does not effect the seasonal index except where it takes the data from the years lesser than Max year selected

 

[Jacob Barnett]

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?

 

[Mavericks334]

Yes, it helps me select the year that i wish to forecast data for. So when i select the year in the slicer it creates a seasonal index for that particular year, not the historical years.

 

[Jacob Barnett]

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])))


BTW, I've no idea why you've posted that other question that is clearly part of this thread. Its pretty bad form!</values(calendar[calendar></values(calendar[calendar>

 

[Mavericks334]

The Other question, is where i would use the values from that, to multiply with the seasonal index that i get from this question. So total two different questions