2023 Week 02 | Power BI: Create layered line/area charts with labels and annotations

Introduction

For our second week of Workout Wednesday 2023 Power BI Edition, we are taking inspiration from a recent Washington Post interactive story titled “See just how bad a year it was for your retirement account — and why”. The article looks at the closing price of the S&P 500, comparing the value at the end of 2022 to various points in the last few years to see how it changed.  

Note that I used the area chart visual to create the report, but you are welcome to use Deneb or another visual of your choice. While I knew I could accomplish this in Deneb, I thought it would be interesting to see how much I could do using default visuals. 

 

Requirements

Create an imported dataset that contains the S&P 500 data, the events data, and a date table. Create the necessary relationships between the tables. 

Create 5 charts: 

  • Chart 1: Show the percentage change in closing price from December 31, 2021 to each subsequent date through December 30, 2022. 
  • Chart 2: Show the percentage change in closing price from December 31, 2020 to each subsequent date through December 30, 2022.
  • Chart 3: Show the percentage change in closing price from December 31, 2019 to each subsequent date through December 30, 2022.
  • Chart 4: Show the percentage change in closing price from December 31, 2021 to each subsequent date through June 30, 2022. 
  • Chart 5: Show the percentage change in closing price from June 30, 2022 to each subsequent date through December 30, 2022.
For all charts: 
  • Display a different color line and shading for values under 0% vs values 0% and over.
  • Change the font color in the title of the chart to match the sign (negative/positive) of the final value in the chart. The color should match the colors used for the line and shading.
  • Create a marker for the last value in the chart (e.g., December 30, 2022 in Chart 1).
  • Label the final value in each chart with the percentage change value. 
  • Create a tooltip report that shows the closing price and the date. For charts 4 and 5, the tooltip report should include the event category and event description ONLY for the dates that have events. 
For charts 4 and 5: 
  • Add a slicer or other visual that allows users to choose which types of events to show on charts 4 and 5.
  •  Add markers only for dates that are related to an event from the events dataset. The markers should be a different color from the percentage change series. 
  • Add a vertical line on the date that represents the earliest date in the timeframe with a related event. This vertical line should respond to the slicer selections.

Dataset

This week we are using data from two sources: 

  1. Historical S&P 500 prices from Nasdaq: https://www.nasdaq.com/market-activity/index/spx/historical. Select the 5Y timeframe and then select the Download Data button. The data will download as a CSV. 
  2. Events data stored on data.world: https://data.world/mlongoria/pbiwow2023wk2events
You can use the data.world connector in Power BI (recommended) or download the Excel file to your local machine. You will need to log in to data.world to retrieve the data, but accounts are free.

Share

After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @JSBaucke@MMarie, @shan_gsd, and @KerryKoloskoAlso make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year. 

Solution

1 thought on “2023 Week 02 | Power BI: Create layered line/area charts with labels and annotations”

  1. You are amazing Meagan;
    It took me around 3 hours to develop it; I have learned many new things while recreating this report;
    Thank you very much;

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top