Running Product in DAX: Calculating Portfolio Returns

Recently a friend of mine asked me how to calculate investment rates of return in PowerPivot.  In order to do this, one must be able to run over a list of values using a multiplication. Currently, DAX does not have a ProductX or MultiplyX function to evaluate an expression in a specified row context followed by a final pass to multiply all computed values. I dealt with this issue in MDX years ago when attempting to do the same.  Although in that case I used the Multiply() .Net stored procedure to achieve the goal within the expression, I still recall seeing how Mosha pointed out a way to mimic a running multiplication using the following formula:

a * b = e^ ( LN(a) + LN(b) )

In DAX, we can leverage the same computation.  In simple terms, one can define a calculated column as:

=POWER( 10,
SUMX(
FILTER( Table1,
Table1[Id] <= EARLIER( Table1[Id] )
),
LOG( Table1[Values] )
)
)

Notice we can leverage the SUMX expression over a table that grows based on the current row context, and calculate the LOG output which is summed at a second pass.  By raising the final scalar output to the power of 10, we achieve the running product effect:

Let’s now apply this technique to solve the question related to investment returns. Drawing from the common financial VAMI definition, we see that we must find a way to apply the logic below when determining returns over time:

Previous Rate of Return x ( 1 + Current Rate of Return )

At any given date, this formula will give us the rate at which the portfolio is performing. Notice that:

• Although the original definition stipulates monthly returns, it is common to use it with daily returns as well.
• The formula requires the use of a running multiplication
I have placed the sample Excel 2013 file for this blog entry here, in case you want to follow the formulas by yourself.  The idea is we will build a hypothetical portfolio and calculate its rate of return.  For this, I downloaded closing prices from 1/3/2012 to 12/18/2012 for three stocks, and uploaded them into a PowerPivot model.

For simplicity, I did not account for dividends and I used the same date (1/3/2012) to establish a position and there were not subsequent buy orders.  Those items can surely be dealt with more DAX logic, but it is not necessary to simply illustrate the point of running multiplications.

I created a linked table with the info below. The quantity of each order serves as a parameter when using portfolio allocation to compute returns:

To first calculate the actual market value of the position, we use a measure defined as:

[Position]:=SUMX(
VALUES(Prices[Ticker] ) ,
CALCULATE(
VALUES( Parameters[Quantity] ),
FILTER(Parameters, Parameters[Ticker] =
EARLIER(Prices[Ticker] )  )
)  *
CALCULATE( MAX(  Prices[Close]  ), LASTDATE( Prices[Date] )  )
)

As we want the Portfolio Total (row total) to reflect the correct amount, we must calculate the value at the grain of the ticker symbol.  Additionally, as there is no actual relationship with the parameter table we must establish one through a calculation.  The result multiplies the order quantity for any given ticker symbol times the closing price for the trade date.  As we also want the column total to generate a valid dollar value, we use LASTDATE in the CALCULATE statement that fetches the closing price.  This is because MAX will get the maximum value for all days that make up the grand total row, but we want is the actual value for only the last date. A partial screenshot of the result shows the following:

We now want to compute the Daily Delta which is actually the daily rate of return.  For this it is necessary to get the last position value on the most current trade date (before today).  The position value, or market value, for trade date minus one is:

[Market Value T-1]:=CALCULATE(
[Position],
FILTER(
ALLEXCEPT( Prices, Prices[Ticker] ),
Prices[Date] =
CALCULATE( MAX( Prices[Date] ),
FILTER( ALLEXCEPT( Prices, Prices[Ticker] ),
Prices[Date] < MAX( Prices[Date] )
)
)
)
)

As we cannot be sure what T-1 will be, we must calculate the MAX date before today, and then retrieve the position value on that date:

The Daily Delta can now be easily calculated:

[Daily Delta]:=IFERROR( ([Position] – [Market Value T-1])/[Market Value T-1], BLANK() )

Notice at this point the Grand Total reflects the actual portfolio daily return, which is not a straight sum or average of the individual ticker symbol returns but instead is calculated at the total market value for the portfolio.

We are finally ready to apply the running product logic in order to determine compounded rates of return:

[Comp ROR]:=POWER( 10 ,
SUMX(
FILTER(
ALL( Prices[Date] ),
Prices[Date] <= MAX( Prices[Date] )
) ,
LOG( 1 + [Daily Delta] )
)
) – 1

For any given symbol or for the entire portfolio, we can now see what our percent gain or loss was at any given point during the life of the position. Charting this will help better understand the overall picture of our investments. In Excel 2013, it could look like this:

The entire report above is built using only the DAX functions discussed, as well as built-in Excel functionality (Moving average trendlines and cube functions are of great help when telling the story of investment performance over an Excel dashboard).  For example, to get the most current report date I used:

="(As of " & CUBEMEMBER("ThisWorkbookDataModel","[Prices].[Date].[All].LastChild") & ")"

Similarly, to get the portfolio return value (14.20 %), I simply queried the model from an worksheet cell with the following formula:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Comp ROR]")

Finally, one of the beauties of Excel 2013 PowerPivot models is that there is no need to reopen the PowerPivot window when updating a linked table value.  Simply refreshing the dashboard through the Data tab will work to generate newly computed values.  As such, what-if scenario analysis is greatly simplified.  For example, and as you can see above from the Symbol % Allocation chart, YHOO was only a small part of the portfolio market value, however it had a great run at the end – which can be verified in the Symbol Returns chart.  If I changed the parameters of my report, and allocated a much bigger order to that symbol as in:

The returns will be now more interesting:

Also, you would notice the three moving averages pointing in one direction: up 🙂

Thinking in DAX: Non-linear Cognition at Play

Over the last year, I have had the opportunity to teach PowerPivot and DAX to a good number of technical and non-technical people in a variety of industries.  After each training, I recount what went right and what went wrong, and I have identified some clues that offer some insight on the way people assimilate this technology and ultimately decide whether or not to link themselves to it.

Perhaps a chart can help explain this better.  Let’s step aside from DAX for a moment, and think about a more traditional subject area which is typically taught in school: World History.

When you are learning World History, each milestone builds on the prior one and there is an incremental process which can be graphed as a curve:

At any give point in the curve you are one step ahead than the prior one.  This, of course, with the exception of very last phase when you have arrived to a theoretical moment of complete assimilation of all knowledge.

If you are learning, for example, about the European Union it is likely you will be building on earlier topics related to World War II. As you follow the chain of topics, you can realistically stop in any of them without harm.

DAX does not  follow this learning pattern.  First of all, even for the some of the most basic Time Intelligence functions like “Running Total since Inception”, there is a need to understand more advanced concepts of filter context.  Due to this, the pattern can instead be charted as the following:

DAX can appear simple (and easy) at first due to its similarities with Excel functions.  Fully additive measures can be created quickly. In some cases, PowerPivot even creates them automatically (such is the case of implicit measures).   And you may quickly hit the sudden understanding that DAX generates portable calculations that can be sliced and diced by other data attributes.

At that point, an analyst using PowerPivot may feel empowered and attempt to generate something a little more complex.  Something, for example, like a “Year over Year Growth” ratio.  As there are no built-in functions to get the answer directly, there is a need to understand how to build your own; for first time users, though, the most likely experience is that one getting stuck and experiencing a bit of frustration.

And here lies the power – and the current weakness – of DAX based tools: they are – for the most part – a blank canvas. To develop some of the advanced, non-additive analytical measures you will want to use, there is a need to understand quite a bit of how the engine interprets incoming computations.   But for those that take the time and interest to learn how the language actually works, doors open to a huge analytical arsenal made up of DAX functions and patterns that can be used to develop virtually any kind of calculation needed.

The flat horizontal lines on my “DAX Learning Pattern” chart happens when the person is cognitively stuck in the “I don’t get it! I just don’t see it!” phase.  During that time, they may feel no knowledge is being acquired. They may be working on a DAX problem for a long time with no feeling of being close to a solution.

The best way I can explain this is through a Gestalt example:

What do you see?  An old woman with a big nose?  Or a young woman turning to the side?

Either way, once you see the first one it will take a bit of work – and concentration – to identify the other one.   You may stay looking at the picture for some time but when you finally see it, it will be so obvious you can’t help but seeing it it now.

In DAX, this can happen when learning about the many powers of the CALCULATE function, or when fully assimilating the fact filter context propagate in one direction by default, or even when understanding how to change the granularity of a calculation using an AggX function.

In essence, what is required here is non-linear thinking:  rather than straight step by step logic, what advances our level of DAX is a sequence of cognitive reframing scenarios in which the solution is assimilated based on sudden understanding – many times associated with a loud ‘AHA!’  -, rather than through paced and gradual knowledge acquisition.

Also note that not all horizontal/vertical lines in the DAX learning pattern chart have the same size.  This is because after a deep reframing that leads to better understanding, there may be a small gain in direct formula applications.  In other cases, a less dramatic reframing can have huge impact in formula applications.  Or vice versa. In a similar way, you may be stuck for a short or long time, when trying to grasp how to get a formula to work, or when analyzing a how a working formula actually computes.

Let’s see an example.  Many of you are already familiar with DAX but for the sake of this example, imagine you are learning about the most relevant functions and you hear their definition for the first time.  In that scenario, lets define a few of them:

AVERAGEX/SUMX – Takes a table and iterates through each row calculating an expression.  As a final pass, all row expression output values are averaged or added (depending on the function selected).

VALUES – Returns a one table column removing duplicates over the column that has been used as a parameter.

That is clear and relatively simple.  When testing those functions (in this case, using AdventureWorksDW), we get the Total Product Cost by using:

)

Also, I could count the products with sales on any given month with the next expression:

)

In the fact table, we have many sales for any given product.  By using VALUES we get the list of unique products with sales. At this point, the implementation of those functions directly reflects the definition given above and the calculation output is straight forward.

Let say somebody asks now to generate a [Total Product Cost] calculation in which, at the monthly level is the default value (sum) but at the yearly level it should be the average of all monthly values

For people learning DAX, this could quickly translate into the flat line on the DAX learning pattern chart. Interestingly, after the example given above – they already know all they need to know in order to get this done.  But it takes some time for the idea to really ‘click’.

Here is the calculation:

)

If you are new to this type of calculation, you may experience the same thing as when you where looking at the old lady picture above, and focusing hard trying to find the young lady in the very same outline.  You may stay there for a while, but then it finally happens: AHA!.    If you create a list of unique month values at the year level, you can take each individual output – which we arrived at by summing – and then average at that level.  However, at the month level, the average will divide by 1 (the number of unique values at that level), so we keep the output SUM value.

Once the sudden re-framing and aha! moment occurs, you can’t stop seeing the pattern every time you look at the calculation.

In the end, one calculation can be interpreted by the xVelocity engine in multiple different ways, even without a single change on the expression. This is because the interaction of query and filter context can generate different output values using data across many tables at multiple granularities.

Some people seem to be pre-wired for this type of thought process.  To others, this doesn’t come very naturally but can definitely be achieved if there is persistence (that is a big if, considering many people in the business world have much more to do than learning a new tool/technology).

What I have noticed is that non-technical people that end up achieving some level of mastery of DAX is because at some point they are able to shift their attention away from the ‘immediate satisfaction’ of solving the problem at hand and dedicate a few extra minutes to experiment how functions behave under different conditions.  In other words, they become interested in the language itself, not just as an intermediary vehicle.

Not every analyst in the business world or technical person assigned BI responsibilities will have the time and interest to make this shift.  Interestingly, however, some of the people I have seen that most closely relate to this experimentation attitude are some of the subject matter experts that in many cases end up providing QA input for data warehousing projects.  In that sense, I believe the conditions for this technology to flourish are there even when it requires a non-linear thought process that may be foreign to some business users.