# Investment Returns Standardized

The prices of individual investment securities can vary widely and thus a common reporting practice is to standardize or index these values to a baseline value such as 100. For example, Apple’s (AAPL) stock price is currently \$188.84 whereas Ford Motor Company’s (F) stock price is currently \$11.88. Without any standardization, a chart of the stock prices for these two securities would fail to indicate relative investment performance.

In this blog post we’ll walk through an approach to A) compute the daily return % for a security via Power Query (M) and B) compute an indexed or standardized asset value via DAX.

## Starting Point: Daily Closing Prices Per Security

Your source data is a three column table with each row representing the closing price of a given security on a given date such as the following:

You can download this data for free from sites such as Yahoo!Finance and it could be stored in a database or other source accessible by Power Query (M), which is basically any source. In this example, the prices for three different securities (tickers) for the past 30 days is stored in Excel with a table name of PriceData.

## Step 1: Daily Return % Column

We now need to add a column to the source table which represents the percentage difference of a price relative to the previous day for a given security (e.g. SPY). Specifically, the calculation logic needs to compare the current and prior day’s price for a given security with the prior day meaning the prior trading day, not prior calendar day.

The following M query implements this logic via a sequential index column and conditional logic for referencing a prior row within a Table.AddColumn() function:

```let
Source = Excel.CurrentWorkbook(){[Name="PriceData"]}[Content],
Types = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Adj Close", type number}, {"Ticker", type text}}),
Sort = Table.Sort(Types,{{"Ticker", Order.Ascending}, {"Date", Order.Ascending}}),
IndexCol = Table.AddIndexColumn(Sort, "Index", 1, 1),
DailyReturn =
if [Index] = 1 then null
else if IndexCol{[Index]-2}[Ticker] <> [Ticker] then null
else
, type number),
ReturnRowsOnly = Table.SelectRows(DailyReturn, each ([#"Daily Return %"] <> null))
in
ReturnRowsOnly```

The sorting variable (step) precedes the index column such that the previous trading day’s price for a security can be directly referenced relative to the current row. With the Daily Return % column defined, the rows without a daily return % value (the first date for a given security) can be filtered out.

The Power Query (M) can now be loaded to Power BI, Analysis Services Tabular, or Power Pivot for Excel. In this example, the M query is loaded to Power Pivot for Excel:

The format for the Daily Return % column is set to Percentage in this example. Note that the sequential index column created to support is hidden from client tools. (As an aside, we now have filtering and sorting capabilities in the Data view of Power BI Desktop per the June 2018 Release)

## Step 2: Standardized DAX Measures

We can now develop DAX measures which standardize the prices of the different securities to a baseline value, 100 in this case. Specifically, these calculations will add the value 1 to each Daily Return % value (in the filter context) and compute the product of these values via the PRODUCTX() DAX function. For example, -.08% will become .99155 for SPY on 5/17 and this .99155 will be multiplied by other daily return values.

Finally, date filter logic can be added to these product calculations to support specific date intervals and a cumulative total (ie since the first date). In this implementation, DAX variables have been used for the product calculation to consolidate the logic of the standardized measures:

```Index Value Cumulative =
VAR CumulativeProduct =
CALCULATE(PRODUCTX('PriceData','PriceData'[Daily Return %] + 1),
FILTER(ALL(PriceData[Date]),'PriceData'[Date] <= MAX('PriceData'[Date])))
RETURN
IF(HASONEVALUE(PriceData[Ticker]),CumulativeProduct * 100,BLANK())

Index Value Date Interval =
VAR ProductDateInterval =
CALCULATE(PRODUCTX('PriceData','PriceData'[Daily Return %] + 1),
FILTER(ALL(PriceData[Date]),'PriceData'[Date] >= MIN('PriceData'[Date])
&& 'PriceData'[Date] <= MAX('PriceData'[Date])))
RETURN
IF(HASONEVALUE(PriceData[Ticker]),ProductDateInterval * 100,BLANK())```

The only difference between these two measures is the date filter logic. The IF…HASONEVALUE() logic protects against multiple securities in the filter context such as subtotals.

A quick look at Index Value Cumulative:

You can now compare and display the securities in an intuitive format. For example, as of 5/22/2018, F had performed best (101.1), XLF was second (100.4), and SPY was third or last at (100.1). Again, this calculation logic includes all the preceding return values up until the given date.

Users will surely want to filter for specific time frames to ask the question: “What if I bought security X on this date and then sold it on that date?” This is where the date interval measure is used: