S&P 500 index is an American stock market index consisting of 500 large companies and is considered to be the one of the best representations of the U.S. stock market, according to Wikipedia. The following dashboard created using Power BI Desktop tracks the index over time.

I’ve also hid a few things in this post for you to find and learn. Those things include: creating a Power BI report from start to end, calculating percentage change from previous year, creating calculated measures, and an animal plush toy.

Here’s the dashboard. It has four items.

• A: An area chart with index price as the measure from 1965 to 2014. This shows how price changed through the years.
• B: A bar chart showing percent change of the price from previous year. Custom formatting is applied to show green and red for positive and negative change respectively.
• C: A card showing the average annual return for the selected time period. There is a calculated measure behind this.
• D: I like dashboards that tell something. Sure, charts A and B go up and down as index price fluctuates, but duh!, everyone knows that. So to put all of this in perspective, this bar chart (D) shows returns if you were to invest $10,000 in S&P compared to returns of the same amount invested in a savings account with 3% interest.

Dynamic Filtering

All items in the dashboard change dynamically based on the filters.

For example, I add a page level filter and include years greater than or equal to 1982 and the dashboard changes accordingly as shown below. You’d have had an average annual returns of 8.71% and $10,000 invested in 1982 would have become $170,000 handily beating the savings account returns.

However, if you had invested at the starting of 2000 and cashed out of the market after the big drop in 2008, results would have been completely different. The average annual returns would have been -5.1% and the invested capital of $10,000 would have shrunk to approximately $6,000.

How to create this dashboard

You like this dashboard? Good, now let’s see how to create it. I like to think that the process of creating a dashboard is the same as writing an email, for example. You need to first know what you want to say. For this dashboard, we already know what we want to say as described in items A through D above.

Next, we need data. I downloaded historical prices for S&P 500 from Yahoo! Finance to a CSV. The downloaded data contains monthly prices from 1965 to 2015. I renamed the file to gspc.

Get Data

Open Power BI Desktop, select “Get Data”, and click on “CSV” as the source type.

Then point to the file you just downloaded. PBI Desktop will show a preview of the data from the CSV file as shown below. We want to edit this data, so click edit.

The query editor will open showing the data as a table.

At this point, let’s add a few steps to transform this data.

• Remove Columns: In the “Home” tab, select all columns but “Date” and “Adj Close” and select “Remove Columns”
• Insert Year: Select “Date” column and select “Add Column”  –> “Date” –> “Year” to extract date from the existing year
• Insert Month: Repeat the same step as above but select “Date” –> “Month” to extract month
• Filter Rows. The downloaded flat file contains data for every month, but for this analysis I’m interested only in yearly prices. So I can simply filter for the month of January. Select on the drop-down icon next to “Month” column and select 1 for January

After applying all the above steps, the Power Query query and table looks as shown below.

Calculate Percentage Change From Previous Year

The next step is to calculate percentage change from previous year. Based on the result table we prepared so far, previous row automatically is the previous year. If this is not the case for you, you can introduce a step to “Sort” the table on “Year” column. So, we can add a new column called “PreviousYear” for example which is [Year] – 1 and merge the table with itself using “Year” and “PreviousYear”. This allows us to access the index value for the prior year, using which we can calculate the percent change.

Let’s first add a new column. In the “Add Column” tab, select “Add Custom Column” and give it a name. I enter “PreviousYear” as the name and “[Year] – 1” as the formula.

Next, click on “Merge Queries” and select “PreviousYear” and “Year” from the top and bottom tables respectively and click “Ok”.

You should see a new column created as shown below.

Click on the icon next to “NewColumn” and select only the “Adj Close” column and click “Ok”. Ensure that “Use original column name as prefix” is selected.

The table in the query editor should appear as shown below with new column at the right.

Now we have values for the current year and previous year in the same row. So, we can add a calculation to obtain percent change. Let’s do that and add a few more transformations.

• Rename Column: Double-click “NewColumn.Adj Close” and rename that to “PreviousAdj Close”
• Add Custom Column: In the “Add Column” tab, select “Add Custom Column”, enter “PctChange” as the column name, and enter “=([Adj Close]-[PreviousAdj Close])/[PreviousAdj Close]” as the formula.
• Remove Columns: Remove “PreviousYear” and “PreviousAdj Close” as they’re not needed anymore

With all the transformations performed so far, the table in query editor should appear as shown below.

We’re done with the query editor; we have sourced the data from a flat file, and transformed it to include and create columns that’re needed. Click “File” –> “Close & Load”. This will take you back to the report view.

We need a few more columns for the report, so let’s create them before jumping into creating the dashboard.

Create a Calculated Column

Create a new column called “PctLegend”. The value of the column should be “Positive” if “PctChange” is a positive number or “Negative” if it’s a negative number. We’ll use this column as a legend of the bar chart later. To create this, click “New Column”, type the following in the formula bar, and hit enter.

A new column named “PctLegend” will be created in the table “gspc”. If the formula doesn’t work for you, ensure that “PctChange” is of type “Decimal Number”.

Creating Calculated Measure

Next we need the average annual return. The formula to calculate this is ([End Value] / [Start Value]) ^ (1/No. of Years)-1. To keep the calculation easy to understand, I’ll create three new measures, one for each of the field used in the calculation.

To create a measure called “TotalYears”, Click “New Measure”, type the following in the formula bar and hit enter.

 TotalYears = COUNTROWS(sp500Yearly) 

Repeat the same steps to create two more measures. Formulas for each of them can be seen below.

 StartValue = CALCULATE( SUM(gspc[Adj Close]),FIRSTNONBLANK(gspc[Date],CALCULATE(SUM(gspc[Adj Close])))) 

 FinishValue = CALCULATE( SUM(gspc[Adj Close]),LASTNONBLANK(gspc[Date],CALCULATE(SUM(gspc[Adj Close])))) 

Using these intermediary measures, Average Annual Return can be calculated by creating a new measure with the following formula.

 AvgerageAnnReturn = POWER([FinishValue]/[StartValue],(1/[TotalYears]))-1 

The last set of measures we need to create are the hypothetical returns of $10,000 invested in S&P 500 and a Savings account with a 3% interest rate. Create the measures using the formulas below.

 SPX = 10000*(POWER(1+[AvgerageAnnReturn],[TotalYears]) ) 

 Savings = 10000*(POWER(1+0.03,[TotalYears]) )  

With this we’ve created all fields we need, and are ready to create the dashboard. Remember we’ll create 4 items in the dashboard (see A through D at the top to refresh your memory).

Creating A: S&P 500 Index Over Time

Click on area chart in the visualizations pane. This will create an empty chart as shown below.

Now drag “Adj Close” to “Values” and “Year” to “Axis” as shown below.

An area chart is created as shown in the image below.

This is not the only way to create a visualization. You can straightaway select the fields you want. PBI Desktop will automatically create a visualization based on those fields. You can then still change the type of visualization and move the fields to the appropriate axis. I like starting with an empty chart because it allows you to put some thought into what you want to create beforehand.

Creating B: Percent Change From Previous Year

Click on the column chart. This will again create an empty column chart like before. Drag “PctChange” to “Values” and “Year” to “Axis”. A column chart will be created as shown below.

Percent change is positive for some years and negative for others as represented by columns above and below 0%. It’ll help the readers focus on this, if we format the columns with custom colors.

Add “PctLegend” to “Legend” and you’ll notice different colors for positive and negative change. You can even add custom colors. Click “Format” (a pen-like icon next to fields) and expand “Data Colors”. You’ll see values of “PctLegend”. Use the drop-down on color next to these values and choose the colors you like. I selected “Red” for “Negative” and “Green” for “Positive”. The column chart for percent change appears as shown below.

Creating C: Average Annual Return

Click on “Card” in the visualizations pane select “AverageAnnReturn”. The “Card” will be created as shown below.

Creating D: Savings vs S&P 500

Click on “Clustered Bar Chart” and drag “SPX” and “Savings” to the “Value” pane. In the “Format” tab, expand “Title” and enter “Returns of a hypothetical $10,000 Investment” as “Text”. The bar chart will be created as shown below.

Closing Bell

In this post, you saw step-by-step instructions of creating a dashboard using Power BI Desktop. Along the way, you learnt using CSV file as a source, calculating percentage change from previous year using Power Query, creating a custom column and creating a calculated measure using DAX, but where is the plush toy?

This type of dashboard is common and can be used in several scenarios. I want to hear from you. For what other business cases can this type of visualization be used to tell a data story? Leave your comment below.