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

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 Relative To date is Thursday May 17, 2018..When I select Activity in Past == ‘Day’, I want to know how we did yesterday (Comparison == 1 for Wednesday May 16, 2018) and how we did before yesterday (Comparison Change ==1 for Wednesday May 15, 2018)..When I select Activity in Past == ‘Week’, I want to know how we did in the past 7 days not including today (Comparison == 1 for Wednesday May 16, 2018-Thursday May 10, 2018) and how we did in the prior 7 days (Comparison Change == 1 for Wednesday May 9, 2018-Thursday May 3, 2018).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 slicedGoal: 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))ReturnIF(ISBLANK(calc), 0, calc)Let’s see how we use these metrics in the correct visuals.A., B.. More details

Leave a Reply