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.
- 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.
- 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:
- 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.
- Events data stored on data.world: https://data.world/mlongoria/pbiwow2023wk2events
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd, and @KerryKolosko. Also 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.
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;
Thanks Meagan! very intuitive of the video!
However, I have a question in terms of a DAX function:
Dt Percent Diff Close 2020 =
var FirstValue = CALCULATE(MAX(‘HistoricalData'[Close/Last]), ‘DATE'[Date] = DATE(2019,12,31))
var LastValue = CALCULATE(MAX(‘HistoricalData'[Close/Last]), LASTDATE(‘DATE'[Date]))
var Div = IF(ISBLANK(LastValue),BLANK(), DIVIDE((LastValue – FirstValue), FirstValue))
return Div
in this function, I thought it would return one value for all rows but it seems that it return a different value for each row based on the close value in that row. May I know the reason?
It has to do with the row context. The date is different on each row, so the answer is different on each row.