Dynamic Visuals Using Date Range Slicers in Power BI Pt. 1
Goal: Create visuals which dynamically change depending on the relative date range selected in a slicer.
This is one of the first Power BI hacks I learned, thanks to Sam McKay. Sam calls the technique multi-threaded dynamic visuals and really what this technique accomplishes is dynamically changing between measures using some fancy DAX equations. Notice, I mention dynamically changing measures, not dynamically changing columns or dimensions. So the measures or y axis in a chart will change, but the x axis (granularity at which the data is presented, in this example the data granularity is at the day level) will remain the same. It is possible to dynamically change columns/dimensions, but this technique did not scale well with our data.
In this example, I will show how you can:
- Create a slicer called ‘Activity in Past’ to switch between seeing your data in the past day, week, month, or quarter
- Create DAX equations to dynamically change metrics and text based on dates ranges sliced
- Create DAX relative equations with unichar symbols ie. calculating percent change between this week and previous week
Download the dashboard here.
Where do we start? The data, of course
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.
Next using the ‘Enter Data’ option in the Query Editor, the table Date Ranges was manually entered. This table has 4 fields: Business Ranges (the business term the user will see), Word Ranges (the real world equivalent of Business Ranges, this will not be used), Days Ranges (specifies the relative date range for each Business Range), and Comparison Ranges (specifies the relative date range for comparing each Business Range).
Finally, USvideos and USvideos Dates have a many to one relationship on trending_date. Date Ranges does not have any relation to any table, this is a supporting table.
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.
- Create a slicer called ‘Activity in Past’ to switch between seeing your data in the past day, week, month, or quarter
Goal: In this section, we will create dynamically changing flags for trending_date, marking date of interest based on slicer selection.
- Create your dynamic slicer using Date Ranges[Business Ranges].
Specify the relative to date. Typically this is today but since this data is not live I had to select a different date with this calculated measure:
Relative To = CALCULATE(MAX(USvideos[trending_date]), ALL(USvideos))- Create a calculated measures which hold the slicer selection made and show the Days Ranges and Comparison Ranges. In plain English the first measure reads: If only one value has been selected in the slicer, then show the days associated with the value.
ie. I select Week, so should see 7 based on the table Date Ranges.
Total Days = IF(HASONEVALUE(‘Date Ranges’[Business Ranges]), VALUES(‘Date Ranges’[Days Ranges]))ie. I select Week, so should see 14 based on the table Date Ranges.
Total Comparison = IF(HASONEVALUE(‘Date Ranges’[Business Ranges]), VALUES(‘Date Ranges’[Comparison Ranges]))- Create a calculated column which find the date difference in days between the trending_date and the Relative To date.
Slicer date check = DATEDIFF(‘USvideos Dates’[trending_date], [Relative To], DAY)- Create the calculated measures which marks a date as fitting within a date range. In plain English for the measure Comparison (this measure will be used to show data in the past N days): If the difference between the trending_date and the Relative To date is less than or equal to the associated days for a selected date range AND if the difference between the trending_date and the Relative To date is greater than 0 mark as 1 else as 0. Note: We use MIN([Slicer date check]) so we can have 1 value selected.
Comparison = IF(MIN([Slicer date check]) <= [Total Days] && MIN([Slicer date check]) > 0, 1, 0)In plain English for the measure Comparison Change (this measure will be used to show data in the past N days/previous N days): If the difference between the trending_date and the Relative To date is greater than the associated days for a selected date range AND if the difference between the trending_date and the Relative To date is less or equal to the associated comparison days for a selected date range mark as 1 else 0.
Comparison Change = IF(MIN([Slicer date check]) > [Total Days] && MIN([Slicer date check]) <= [Total Comparison], 1, 0)Let’s see this in action!
The two most important metrics moving forward are Comparison and Comparison Change. So make sure to take the time to ensure that these are working how you need them to work.
2. Create DAX equations to dynamically change metrics and text based on dates ranges sliced
Goal: In this section, we will create dynamically changing text (metrics A. and C.) and value (metric B.) calculated measures. 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 text (metrics A. and C.) is pretty simple and just requires a SWITCH statement. In plain English for metric A.: When the value for Business Ranges is Day display ‘VIEWS YESTERDAY’, when the value for Business Ranges is Week display ‘VIEWS THIS WEEK’, etc. Note: the leading spaces are part of the ordering hack. For metric A.:
Slicer text views = SWITCH(TRUE(),
VALUES(‘Date Ranges’[Business Ranges]) = “ Day”, “VIEWS YESTERDAY”,
VALUES(‘Date Ranges’[Business Ranges]) = “ Week”, “VIEWS THIS WEEK”,
VALUES(‘Date Ranges’[Business Ranges]) = “ Month”, “VIEWS THIS MONTH”,
VALUES(‘Date Ranges’[Business Ranges]) = “ Quarter”, “VIEWS THIS QUARTER”,
“”)For metric C.:
Slicer text relative = SWITCH(TRUE(),
VALUES(‘Date Ranges’[Business Ranges]) = “ Day”, “VS PREVIOUS DAY”,
VALUES(‘Date Ranges’[Business Ranges]) = “ Week”, “VS PREVIOUS WEEK”,
VALUES(‘Date Ranges’[Business Ranges]) = “ Month”, “VS PREVIOUS MONTH”,
VALUES(‘Date Ranges’[Business Ranges]) = “ Quarter”, “VS PREVIOUS QUARTER”,
“”)- Creating dynamically changing metrics (metric B.) is pretty straightforward since we have our Comparison metric. In plain English for metric A.: Filter the table USvideo Dates where Comparison data flag is True, take the sum of views based on the date filtering, and if the sum if blank return 0 and if the sum is not blank return the sum.
Number of Views Sliced =
var calc = CALCULATE(SUM(USvideos[views]), FILTER(‘USvideos Dates’, [Comparison] = 1))Return
IF(ISBLANK(calc), 0, calc)
Let’s see how we use these metrics in the correct visuals.
3. Create DAX relative equations with unichar symbols ie. calculating percent change between this week and previous week
Goal: In this section, we will create a dynamically changing KPI for percentage change between recent and past values (metric D.). This metric utilizes unichar variables together with a percentage change calculation. There are many ways that you can use unichars to improve your visualizations.
A silly but very necessary thing to note is that 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 text. Next, create your calculation. In plain English this change calculation reads if ‘DAY’ was selected on the slicer: Subtract the views yesterday and views from the day before yesterday, divide this value by the views from the day before yesterday, and if there is an error (this can occur when dividing by 0) then return a blank. 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 Change = IFERROR(
([Number of Views Sliced] — CALCULATE(SUM(USvideos[views]), FILTER(‘USvideos Dates’, [Comparison Change] = 1)))/CALCULATE(SUM(USvideos[views]), FILTER(‘USvideos Dates’, [Comparison Change] = 1)),
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%”))
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.
Recommendations
- Take best practices when creating your “sliced” calculations. Use Count vs. Count (Distinct) and always try to simplify your calculations as best as possible. People hate waiting for analytics, no matter how pretty they are.
- Like always, perform a lot of quality assurance on your data results!
Concluding Remarks
Creating dynamic visuals using date range slicers or other slicers is very powerful and can help create a great dashboard for seeing data for different ranges. In my experience, customers love seeing these type of dashboards but if they are data savvy there will want more control, so sometimes a simple date slicer with the ability the export the data is just a better approach. 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. Also, if you would like to learn more about Seismic Software and how we use Microsoft’s Power BI, visit us.
