2023 Week 33 | Power BI: Date Slicer

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.