Introduction
Welcome to the Workout Wednesday Week 32! This week’s challenge will focus on how to create an advanced date slicer in the Power BI report. A user-friendly and modern-looking slicer to level up your dashboards. You can create your own designs, share them with colleagues and control the preview.
To re-create, I am using stock market data for past 15 years, where I need to quickly switch between time periods and highlight important dates. For the slicer, I am using Powerviz Date Picker Visual.
Be sure you’re using the August 2023 version of Power BI.
Requirements
Get Data
Get the source data from the link below, or you can use any other company’s stock data.
· Import the data.
· Add another table for dates. I am using the extended date table by Melissa. Thanks, Brian, for introducing it. Set the start and end periods to generate the table. or create a custom calendar table.
· Click “Close & Apply”.
Build Visual
1) Before visuals, create some measures
a) OLCH (Open Low Close High) = If close is greater than open price, show 1, otherwise 0.
b) OLCH Tooltip = If close is greater than open price show HIGH; otherwise, show LOW.
c) 2020 = If year is 2020, give output as 1
d) Last 3 Years = If year is 2021,2020,2019, the show 1 otherwise 0.
e) Custom =
SWITCH(TRUE(),
MIN(HINDUNILVR[Date]) = DATE(2021,01,15), 1,
MIN(HINDUNILVR[Date]) = DATE(2021,04,16), 1,
0)
2) Create a Line Chart
a) Drag Date in X-axis and Close price in Y-axis.
b) Select line style as “Step”.
c) For line color and visual theme, I am using green color (#31A47B)
d) Add custom label and use “OLCH Tooltip” here.
e) Enable “Find Anomalies” option under Analytics tab. I am using daily high and low prices column to detect any anomaly at 80% sensitivity.
3) Create Date Slicer with Calendar view
a) For this example, I am using Date Picker by Powerviz.
b) Drag Date in Date field, OLCH in Holidays, OLCH, 2020, Last 3 Years in Presets, Custom in Default Selection
c) A date picker setup will open > go to “Pick A Theme” stage > Select Custom > Upload this given JSON file here. Click Done.
d) If you’ve followed the exact same data and names with this template your slicer is ready!
e) Otherwise, open the advanced settings at top-right. Update the columns there. You can also pick one of the given themes.
f) Using “Holidays” with the “OLCH measure”, you can highlight the dates where market closed at a high.
g) If you just want to filter the line chart with dates where market closed at a high. Disable the date range toggle and select “High” presets.
To improve the design, follow these steps:
– Use same color. My report primary colors are (#31A47B, #2F2F2F, #FFFFFF, #F2F2F2)
– Add a text note to denote what markers on dates represent.
– Set default selection, to control the period when the user refreshes the page.
Dataset
This week’s data can be found at Github : https://github.com/kolky001/Workout-Wednesday-2023/blob/main/StockData.csv
Share
After you finish your workout, share on Twitter using the hashtags #WOW2023 and #PowerBI, and tag @MMarie, @shan_gsd, @KerryKolosko. If sharing to LinkedIn be sure to tag Kavita Behera. 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.
Solution
Solution File available for download via https://github.com/kolky001/Workout-Wednesday-2023/blob/main/Wow-Week23-StockData-Solution.pbix
Coming soon
The link to the data is incorrect. It points to an Excel file in the same Github repository.
Fixed. Thanks.