Dynamic Visuals Using Date Range Slicers in Power BI Pt. 2

Goal: Create visuals which dynamically change depending on the date range picked in the slicer.

This is a continuation of my first blog on creating relative date range slicers to dynamically change visuals. Previously to deliver a feature request, I created a slicer to switch between seeing the past day, week, month, or quarter of activity along with seeing the according time percent change metrics. Still, new feature requests came in and end users wanted more flexibility in choosing their dates via a native date picker so I gave it a go and created the necessary DAX calculations to make the customers happy.

In this example, I will show how you can:

1. Create a slicer called ‘Trending Date” to pick through dates and see relative metric values.
2. Create DAX equations to dynamically change metrics and text based on dates ranges sliced

Completed dashboard which utilizes dynamic date slices to change all the metrics in the visual.

Download the dashboard here.

Where do we start? The data, of course

As in Part 1, I am using Kaggle’s Trending YouTube Video Statistics data set which contains several daily trending metrics for top videos across several countries (only US data or table USvideos is used in this exercise). After importing USvideos into Power BI, I created USvideos Dates by duplicating USvideos, dropping all columns except trending_date and removing duplicate dates. So I have 2 tables, USvideos which has all my daily trending metrics tied to videos and USvideos Dates which is just a unique list of all my dates of interest.

Tables USvideos and USvideos Dates derived from Kaggle’s Trending YouTube Video Statistics data set.

Although we can make a many to one relationship between USvideos and USvideos Dates on trending_date, this relationship will not be active.

Inactive relationship between tables USvideos and USvideos Dates.

Let’s Build This!

Before actually building out your dashboard, I would highly recommend troubleshooting the following metrics to make sure that they are working properly.

1. Create a slicer called ‘Trending Date” to pick through dates and see relative metric values.

Goal: In this section, we will create dynamically changing flags for trending_date, marking dates of interest based on date selection.

• The date range slicer you will create will use the trending_date field from the USvideos Dates table. So go ahead and create the date range slicer (I choose ‘Between’ for the type of date slicer I want).
• Create calculated measures to capture the start and end date in the date range slicer.

Min Trending Date = CALCULATE(
 MIN(‘USvideos Dates’[trending_date]),
 ALLSELECTED(‘USvideos Dates’[trending_date]))
Max Trending Date = CALCULATE(
 MAX(‘USvideos Dates’[trending_date]),
 ALLSELECTED(‘USvideos Dates’[trending_date]))

• Create calculated measures to capture the start and end date relative to the date range slicer. Ex. If in the ‘Trending Date’ slicer we want to see data between 5/10/2018–5/17/2018 (a range of 8 days), we will want to see relative metric comparisons for 8 days prior to 5/10/2018 or 5/2/2018–5/9/2018.

Trending Dates Diff = DATEDIFF([Min Trending Date], [Max Trending Date], DAY) + 1
Comparison Min Activity Date = [Min Trending Date] — [Trending Dates Diff]
Comparison Max Activity Date = [Min Trending Date] — 1

Let’s see it in action!

In the first example I am interested in Activity from 5/15/2018–5/17/2018, my Date Flag shows 1s for the appropriate dates and my Comparison Date Flag shows 1s for the relative date from 5/12/2018–5/14/2018, a trending date difference of 3 days. Troubleshoot as above to see correct functionality.

2. Create DAX equations to dynamically change metrics and text based on dates ranges sliced

Goal: In this section, we will create dynamically changing value (metric A.), text (metrics B.) and KPI for percentage changes (metric C.). These metrics will be used within card and table visuals, but for bar/line charts a slightly different approach will be used.

• Creating dynamically changing metrics (metric A.) is pretty straightforward since we have our Comparison metric. In plain English for metric A.: Filter the table USvideo Dates where the Date Flag is True, take the sum of views based on the date filtering, and if the sum is blank return 0 and if the sum is not blank return the sum.

Number of Views = SUM(USvideos[views])
Number of Views Sliced =
var calc = CALCULATE([Number of Views], FILTER(USvideos, [Date Flag] = 1))
Return
CALCULATE(IF(ISBLANK(calc), 0, calc), ALL(USvideos[title]))

• To create the dynamically changing text (metric B.) all you need is string concatenation and the field Trending Date Diff created above.

Relative to = “VS PREVIOUS “ & [Trending Dates Diff] & “ DAYS”

• The KPI for percentage change metric (metric C.) utilizes unichar variables together with a percentage change calculation. Note: Although unichars are supported in Power BI, when publishing to web/embedded/etc. make sure that the font in the visual utilizing this metric is web supported. I use Arial. Creating this dynamically changing KPI occurs in a couple of steps. First, you must declare your unichar variables as specific symbols, here is a good reference for finding unichars but again you need to see if they are supported by the font. Next, create your calculation. In plain English this change calculation reads determine the previousValue for the relative prior date range and determine the change from the currentValue or Number of Views Sliced and the previousValue. Finally, using a switch statement will let you output the KPI by incorporating the unichar with the change calculation formatted as a percentage.

Number of Views Change =
VAR Down = UNICHAR(9660)
VAR Up = UNICHAR(9650)
Var Constant = UNICHAR(9654)
VAR previousValue = CALCULATE([Number of Views], FILTER(USvideos, [Comparison Date Flag] = 1))
VAR Change = IFERROR(([Number of Views Sliced] — previousValue)/previousValue, BLANK())
RETURN
SWITCH(TRUE(),
ISBLANK(Change), “-”,
Change < 0, Down & FORMAT(Change, “0.0%”),
Change > 0, Up & FORMAT(Change, “0.0%”),
Constant & Format(Change, “0.0%”))

Let’s see how we use these metrics in the correct visuals.

A., B. For card and table visual you should use the “sliced” calculations, with the table can be filtered even further to not display value where a particular metric is = 0. C. For bar charts use the non-sliced metrics (ie. just sum of views rather than Number of Views Sliced) and use Date Flag== 1 as a Visual level filter to make sure that the correct bars are showing.

Taking all the above steps, applying some design on your dashboard, and you are done. You now have a dashboard which dynamically changes based on the date slicers selected and gives your customers more flexibility to pick their dates.

Conclusion

With any new feature request, understand why something is requested, research if it is possible, build out the feature testing frequently, and deploy if the experience is as expected. The data, any code, and PBIX file in the post is available here. If you have any questions or thoughts on the tutorial, feel free to reach out in the comments below or through Twitter. If you want to learn how to filter and compare different time periods with Power BI, check out this great blog post.

Additional Blogs

Dynamic Visuals Using Date Range Slicers in Power BI Pt. 1