In this post I want to analyze a data model that perform some basic computations over stocks and uses one of the most commonly needed formula there (and in many other business scenarios): a moving average over a specified period in time. Moving averages can be very easily modeled in PowerPivot, but there is the need for some care and, in this post, I try to provide a solid background on how to compute moving averages.

In general, a moving average over period T1..T2 is the average of all the values between T1 and T2. Sounds easy, so why do I feel the need to write a post about it? Because in stock fixings, as in sales and many many other scenarios, the moving average cannot be computed that easily. If the period is – say – 10 days, then it can contain one or more holidays inside. during holidays, there is no data and this does not mean that the value during holidays does not exists, it means that holidays should not be used to compute the average. Moreover, different periods contain a different number of vacation days, making things a little bit more complicated.

The business scenario is clear, let us describe the data model. We basically have one table, called Fixing, which contains the value of stocks for the last 10 years:

There are a lot of columns here, since this is the data structure provided by my trading system but we are interested in only three columns, for the sake of this example:

- Stock: the name of the stock, i.e. Microsoft.
- Date: the date of the values
- Close: the final price of the stock

As a very gross rule of thumb, if the price of the stock is higher than the moving average over a period of time, then the price of the stock will further increase, if it is below the moving average, it will decrease further. Normally two moving averages are used in conjunction and the points where the stock is changing direction are detected by the intersection of the two moving averages.

Thus, we want to use PowerPivot to produce a chart like the following one, which represents the last three years of the Microsoft stock. When the red line crosses the green one going down, it is time to sell, when it crosses going up it is time to buy. If you use this rule to trade… don’t blame me for any loss, I only tried to explain why moving averages are pretty useful in the stocks market.

Now, let us get back to business: how do we compute the moving average? In a previous post, I spoke about how to compute the difference in working days between two dates. This scenario is very similar since the moving average over 50 days is, in fact, the average of the last 50 “working” days of the trading market, i.e. the dates where the value of the stock exists in our table. Thus, the problem is now shifted to “how do I compute the date that is 50 working days before the current one?”.

The answer is pretty easy: first of all we need to assign to each date in the tale an incremental number, counting the number of dates before the current one. Clearly, since we have many different stocks in the table, we must count only the dates where the price of the current stock exists. Let us add a calculated column to the Fixing table with this formula:

DayNumber=COUNTROWS (
FILTER (
Fixing,
Fixing[Date] <= EARLIER (Fixing[Date])
&& Fixing[Stock] = EARLIER (Fixing[Stock])
)
)

Now each row in the Fixing table has a number that uniquely identifies each date for a stock. Let us add a new calculated column to hold the date that represents “50 working days before now”:

FirstDateOfRange50=CALCULATE (
VALUES (Fixing[Date]),
FILTER (
Fixing,
Fixing[DayNumber] = EARLIER (Fixing[DayNumber]) - 50
&& Fixing[Stock] = EARLIER (Fixing[Stock])
)
)

Basically, we use CALCULATE to get VALUES of the Date column in a filter context that shows the only one row that has a DayNumber equals to the current day number less 50. Now that we have the value of the first date of the range, knowing that the last date of the range is always the current date, we can compute the moving average over 50 days using this formula:

=CALCULATE (

AVERAGE (Fixing[Close]),

FILTER(

Fixing,

Fixing[Date] >= EARLIER(Fixing[FirstDateOfRange50])

&& Fixing[Date] <= EARLIER (Fixing[Date])

&& Fixing[Stock] = EARLIER (Fixing[Stock])

)

)

The key here is the FILTER expression, that creates a filter context that shows 50 rows from the Fixing table, exactly the 50 rows that are needed to compute our moving average. Repeating the same procedure for the 200 days average, you end up with this table inside PowerPivot:

Et voilà, the hard part of the work is done. The remaining work is just to add a PivotChart as in the following figure. where we have selected Apple instead of Microsoft:

As you can see from the figure, I added the Calendar table to the data model with the necessary relationship to be able to add the year to the chart but, this time, I did not use the Calendar table to perform working days computation since the addition of the stock name makes the model a bit more complex (a date might be “working” for a stock but not for another one) and the DAX formulas need to be more complex too.

This is just another example of what PowerPivot can do for your reporting needs. If you want to touch the real Karma of PowerPivot, don’t miss the opportunity to follow one of the workshops I and Marco Russo are bringing all over Europe in the next few months.

You can find more info on www.powerpivotworkshop.com. Hope to see you there!

## Comments

## Alberto Ferrari said:

In a previous post , I have used a stock exchange scenario to speak about how to compute moving averages

## Theo van Maurik said:

Hi Alberto

I have a question regarding the filter function. I tried to replicate this post by using the values of several indices. I tried to calculate the daynumber as described above. However i did receive an error stating that the operation was canceled because of shortages of memory.

I tried to optimize the querry. I was able to solve this memory issue by switching the filters in the querry:

DayNumber=COUNTROWS (

FILTER (

Fixing,

Fixing[Stock] = EARLIER (Fixing[Stock])

&& Fixing[Date] <= EARLIER (Fixing[Date])

)

)

Is it true that order of the filters can have an influence on the performance of the filter function?

## AlbertoFerrari said:

@Theo,

Can you send me the workbook by e-mail? I would really love to investigate this a bit more, you probably tested it on a lot more data and... well, it soundes really interesting. :)

## Romit said:

I am seeing a circular reference on the FirstDateOfRange50 (in my sample file). Any idea what may be going on? I have virtually the same fields in my file. The only difference I see, my PowerPivot is based off an Excel linked table vs a database.

## AlbertoFerrari said:

Yep, take a look here:

http://www.sqlbi.com/articles/understanding-circular-dependencies/

In SQL 2012 the circular dependency algorithm has been modified. The example I wrote works in PowerPivot 1.0, in 2012 you will need to add a key column to the table or use ALLEXCEPT to avoid circular dependencies.

## Romit said:

Thanks, Alberto. My "table" is actually a linked Excel table, so I don't see Row Identifier (or any other) table property. I guess that means my only option is using ALLEXCEPT?

## AlbertoFerrari said:

Romit,

You only need to add a unique column to your table (a RowId, just an increasing number), then go to the PowerPivot window, advanced tab, table properties and mark it as the row identifier.

This will set that column as the primary key of the table, and let you avoid circular dependencies.

HTH

## Romit said:

Alberto, I don't see the Advanced tab :-( I only see Home, Design, and then Linked Table under Table Tools.

## Romit said:

Never mind. I went to Advanced Mode and I see it. Thanks! :-)

## Marc said:

Alberto/Theo:

Did either of you determine if there was a benefit with switching the order of the filter clauses using EARLIER? I am running into similar memory issues during processing and am trying to determine if there is a DAX related solution before I request more RAM in the server.

Thanks,

Marc

## Nicolas said:

We cannot solve it adding a row count: how about the future updates? Let´s say we need to remove a symbol or a date, or add new ones..

Any other way to solve this? Also, can you update the formulas for the 2013 version? I can´t find how to do it properly.

Thanks.

## Matt said:

Alberto, thank you very much for your sites and book.

I´m learning a ton from you.

I have a question about this formula:

DayNumber=COUNTROWS (

FILTER (

Fixing,

Fixing[Date] <= EARLIER (Fixing[Date])

&& Fixing[Stock] = EARLIER (Fixing[Stock])

)

)

Let´s say that we want to run it in a table that have tens of millions of rows. The filter function wouldn´t work well with such amount. Is there any way around this?

Thanks in advance.

## AlbertoFerrari said:

@Matt,

If you have lots of rows, then it would be much better to move this kind of calcuations in the ETL phase, avoiding calculated columns in SSAS (or Power Pivot).

As you noticed, there might be a performance problem when computing these expressions over large datasets.

Calculated column are not to be considered as a full replacement for ETL, they might help in several cases and they are a powerful modeling tool, but ETL is still useful :)

## Matt said:

Thanks for your response. I´m new to this, and am fascinated.

Can you recommend a good (and free if possible) ETL tool to perform these tasks? (I just want to run the data on my machine, currently using Access for the big table (500mb)).