Seasonal safety stock in Power Pivot

Seasonality is an important phenomenon to consider for many businesses, and in the context of this post refers not only to seasons in terms of Winter, Summer, and so on, but will also consider how you can report on business activity in terms of custom-defined seasons.

To state the obvious, an ice cream shop might sell less ice cream during winter than it sells during summer. Clothing, cycling gear and even chocolate are also products that have seasonal elements in their trade. There are countless more examples, but the point is that you can plan your sourcing and/or manufacturing activity in accordance with seasonal demand. Doing so might have some significant financial and logistical implications too – why would you want to have the previous season’s stock, taking up room (and tying up money) in a shop or warehouse where you need space for the current season’s product? Does the product have a short shelf life, or is it sensitive to temperature fluctuations from one season to the next, in which case you really only want to buy enough stock or raw materials for the relevant season.

You may even decide that you want the amount of safety stock you hold to depend on the season, or schedule promotions by season.

Defining the Seasons

Central to your ability to analyse your data on a seasonal basis, is the definition of the seasons you choose to adopt. For instance, do you have only two seasons (busy and not busy), three, four or sixteen seasons? This step is the most important to nut out before continuing.

Let’s use an example where we have four seasons, as defined for Australia in general:

Summer: December to February
Autumn: March to May
Winter: June to August
Spring: September to November

Next is to bring those seasons into your calendar table. Depending on whether you used Get & Transform (Power Query) or Power Pivot to construct your Calendar table, the syntax used for doing this would vary accordingly.

Let’s assume you constructed one using the Calendar construction method described here, starting at 2015.

A Switch(True(),…) calculated column can be used to identify the season of interest:

Season =
    TRUE (),
‘Calendar'[Month Number] = 12
|| ‘Calendar'[Month Number] <= 2“Summer”,
‘Calendar'[Month Number] <= 5“Autumn”,
‘Calendar'[Month Number] <= 8“Winter”,

Add a Season Index

Next we want to add a counter, or index to the Season, but if we just count the seasons as defined above, we’ll only get a maximum of 4. Perhaps we can combine the Season with the Year value, and therefore get a unique season for each year? Not a bad guess, but there’s a complicating factor here.

The problem with how our seasons are defined, is that the same Summer always spans across two different Year values:


If we simply combined Season with Year to obtain our unique index for each season as follows:

SeasonYear = ‘Calendar'[Season] &” “&’Calendar'[Year]

we would get the same index value for Summer 1 (Jan & Feb) and Summer 2 the following December, which falls in the same year. Our index values would effectively mix Summer 1 with Summer 2, Summer 2 with Summer 3, and so on. Not good.

How then, do we get the appropriate index number?

My preferred way is to consider again the definition of the seasons, as well as the starting year of the calendar. The first year in my calendar is 2015, so by varying the number I subtract from the year value depending on the month number, I can ensure that Summer 2 in Dec 2015 yields the value “Summer 2” and Summer 1 in 2015 yields the value “Summer 1” with the following formula:

‘Calendar'[Season] & ” “
        TRUE (),
‘Calendar'[Month Number] >= 12,
‘Calendar'[Year] – 2013,
‘Calendar'[Year] – 2014

So when I consider year 2015, I subtract one less year from 2015 in December, making the index value one larger than that for any other in 2015.

The numeric counter, or season index is then calculated, referring to the SeasonNumber column as the value we wish to count:

 SeasonIndex =
    DISTINCTCOUNT ( ‘Calendar'[SeasonNumber] ),
    FILTER ( ‘Calendar’, [Date] <= EARLIER ( ‘Calendar'[Date] ) )

The SeasonIndex value provides you with a unique numeric value for each chronological Season in your calendar.

“Why do I need a Season index?”, you may ask. Well, having a numeric index for the season allows us to do some clever stuff in analysing sales (or budgets), such as calculating the average product sales for a particular season in a particular year. Calculating just the average for a particular year, or the average for one of our four seasons (by implication all years), would give us quite a different result.

Your calendar should look something like this now (I deleted day of week detail and hid the columns I don’t want to be available in my Pivot table fields):


Remember to sort the Month column by the Month Number and the FinMonth column by the FinMthNum, otherwise your graphs and pivot tables will sort months alphabetically.

Evaluate by Season

Let’s imagine you have a table of product quantity per month, for a period spanning a few years. The quantity can be a mixture of sales and budget figures, depending on timing:


I imported the numbers in the table to Power Pivot using Get&Transform (Power Query), where I converted the Yearmonth values to a date field. This allows me to create a relationship to my Calendar using the Date column. After that I can report on the sales by season, as it is already in my Calendar:

Average qty per Season

Say for instance I wanted to know what the average sales per season was, superimposed on my existing pivot chart. Hint: Adding a measure that is just the Average( ) of the sales qty to the chart won’t look any different to our existing ProductQty measure using Sum( ) as aggregator, because the evaluation context that the pivot table provides is too granular to see the effect of average aggregation. The desired measure needs to delve a little deeper. Enter AVERAGEX:

 SeasonAvgProdQty =
    FILTER (
        ALL ( ‘Calendar’ ),
‘Calendar'[Seasonindex] = MAX ( ‘Calendar'[Seasonindex] )

Adding this to the pivot chart (with some formatting to highlight the average values and diminish the monthly values) results in the following:


See how useful that Season index has become? The same measure can be added to a pivot table, of course, if you wanted to see the actual values.

So what else could you use the seasonal average value for? Well, how about that safety stock buffer we talked about earlier? You might have a view that you always want to maintain a safety stock buffer of ten percent above the average seasonal budget.

Safety stock linked to Seasonal Averages

You can probably already guess how this measure will look:

 SafetyStock =
1.1 * [SeasonAvgProdQty]

Safety Stock

Predictably, it will just always be 10% higher than the seasonal average value.

Depending on your data, you might decide that working with four seasons provides too much granularity. In the example above, I would be tempted to combine autumn with winter, and spring with summer, resulting in a Warm and a Cold, or perhaps a Dry and Wet season. This would require a redefinition of the previous seasonal split:

Season =
    TRUE (),
‘Calendar'[Month Number] >= 9
|| ‘Calendar'[Month Number] <= 2“Warm”,

We would also need to amend the SeasonNumber definition, as the season change where we span across two distinct Year values now starts in month 9 instead of month 12:

SeasonNumber =
‘Calendar'[Season] & ” “
        TRUE (),
‘Calendar'[Month Number] >= 9,
‘Calendar'[Year] – 2013,
‘Calendar'[Year] – 2014

This results in the following average spread:

Safety Stock2

Hopefully you’ve learned something, let me know in the comments if you found it useful, and feel free to share with your fellow Power Pivot fans.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s