Sean set the challenge this week to build a custom relative date filter. He uttered the words “this week is pretty straightforward” which always makes me a bit nervous…

The premise was to essentially replicate the out of the box relative date filter. I had a good play with Sean’s solution, hovering my mouse over various fields, to see if there were any clues to get me started. I deduced the filter box, was a floating container, which stored multiple other worksheets and some parameters used to drive the behaviour.

I worked out that I think I’d need to build at least 4 sheets – 1 for the main chart, 1 to manage the date part ‘tabbed’ selector at the top of the relative date control, 1 to manage the radio button selections within the relative date control, and 1 to display the date range.

So let’s crack on.

Building the initial chart

I used the Superstore data from v2022.1, so my dates went up to 31 Dec 2022.

We need to create a line chart that shows the dates at a user defined date level – we need a parameter to control this selection

pDateLevel

string parameter containing the relevant date parts in a list, and defaulted to ‘Week’. Notice the Value fields are all lower case, while the Display As as all proper case. The lower case values is important, as this parameter will be fed into date functions, which expect the string values to be lower case.

Then we need a new date field to show the dates at the level specified

Display Date

DATE(DATETRUNC([pDateLevel], [Order Date]))

Then add this field onto Columns as a continuous exact date (green pill) and add Sales to Rows. Show the pDateLevel parameter and test the chart changes as the parameter does.

Name this sheet Chart or similar. We’ll come back to it later, as we need to ensure this data gets filtered based on the selections made in the relative date control.

Setting up a scaffold data source

So there were no additional data source files provided, and also no ‘you must do it this way’ type instructions, so I decided to create my own ‘scaffold’ data source to help with building the relative date control. Doing this means the concept should be portable if I wanted to add the same control into any other dashboards.

I created an Excel file (RelativeDate_ControlSheet.xslx) which had the following sheets

DateParts

A single column headed Datepart containing the values Year, Quarter, Month, Week, Day

Relative Options

3 columns Option ID (containing values 1-6), Option Type (containing values date part, n periods, to date) and Option Text (containing values Last, This, Next, to date)

I added both of these sheets as new data sources to my workbook (so I have 3 data sources listed in my data pane in total).

Building the Date Part Selector

From the DateParts data source, add the Datepart field to both the Rows and Text fields. Manually re-sort by dragging the values to be in the required order.

Manually type in MIN(1) into the Rows shelf and edit the axis to be fixed from 0-1. Align the text centrally.

We’re going to need another parameter to capture the date part that will be clicked.

pDatePartSelected

string parameter defaulted to ‘quarter’ (note case)

We also need a new calculated field to store the value of the date parts in lower case

Datepart Lower

LOWER([Datepart])

and then we can use these fields to work out which date part has been selected on the viz

Selected Date Part

[pDatePartSelected] = [Datepart Lower]

Add this to the Colour shelf, and adjust colours accordingly (False is white).

Remove headers, column/row dividers, gridlines and axis, and don’t show tooltips.

Finally, we’re going to need to ensure that when a value is clicked on in the dashboard, it doesn’t remain ‘highlighted’ as we’re using the colouring already applied to define the selection made.

In the DateParts data source, create new calculated fields

True

TRUE

False

FALSE

and add both these fields to the Detail shelf, along with the Datepart Lower field. We’ll refer to all these later when we get to the dashboard.

Building the Relative Date Radio Button Selector

In the Relative Options data source, create the following fields

Row

IF ([Option ID] %3) = 0 THEN 3
ELSE ([Option ID] %3)
END

Column

INT([Option ID]<=3)

These fields will be used to define where each entry in our scaffold data source will be positioned in the tabular display.

We then need to get all the text displayed as is expected, but firstly we need to define the parameter that will capture n number of days, which is incorporated into the label displayed

pSelectPeriods

integer parameter

We can now create

Option Label

IF [pDatePartSelected] = ‘day’ THEN
IF [Option ID]=1 THEN ‘Yesterday’
ELSEIF [Option ID] =2 THEN ‘Today’
ELSEIF [Option ID] = 3 THEN ‘Tomorrow’
ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’
ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text]
END
ELSE
IF [Option Type] = ‘date part’ THEN [Option Text] + ‘ ‘ + [pDatePartSelected]
ELSEIF [Option Type] = ‘n periods’ THEN [Option Text] + ‘ ‘ + STR([pSelectPeriods]) + ‘ ‘ + [pDatePartSelected] +’s’
ELSE UPPER(LEFT([pDatePartSelected],1)) + MID([pDatePartSelected],2) + ‘ ‘ + [Option Text]
END
END

This looks a bit convoluted and could possibly be simplified… it grew organically as I was building, and special cases had to be added for the ‘yesterday’/’today’/’tomorrow’ options. I could have added more detail to the the scaffold to help with all this, but ‘que sera’…

Add Column to Columns and Row to Rows. Add Option ID to Detail and Option Label to Text. Manually re-sort the columns so they are reversed

We’re going to need to identify which of the options has been selected. This will be captured in a parameter

pOptionSelected

integer parameter defaulted to 2

and then we need to know if the row of data matches that selected, so create a calculated field

Selected Relative Date Option

[Option ID] = [pOptionSelected]

Manually type in to columns Min(0.0), then change the mark type to shape and add Selected Relative Date Option to the Shape shelf, and amend the shapes to be an open and filled circle. Change the colour to grey.

As before, remove all headers, axis, gridlines, row/column dividers and don’t show the tooltip. Also we’ll need those True and False fields on this viz too – you’ll need to create new calculated fields in this data source, just like you did above, and then add them to the Detail shelf.

Restricting the dates displayed

Now we need to work on getting the line chart to change based on the selections being made that get captured into parameters. But first we need one more parameter

pAnchorDate

date parameter, defaulted to 31 Dec 2022

From playing with Sean’s solution, what date is actually used when filtering can vary based on the date part that is selected. Eg if you choose Month, next 2 months, anchor date of 15 Dec 2022 and choose to display data at the day level, you’ll see all the data for the whole of December rather than just up to 15 Dec (there is no Jan 23 data to display – so you actually only see 1 month’s worth of data). Based on these observations, I created another calculated field to run the filtering against.

In the Superstore data source, create

Compare Date

DATE(DATETRUNC([pDatePartSelected],[pAnchorDate]))

Then from this, I created

Dates To Show

CASE [pOptionSelected]
WHEN 1 THEN ([Order Date] >= DATEADD([pDatePartSelected],-1,[Compare Date]) AND [Order Date]< [Compare Date])

WHEN 2 THEN ([Order Date]>= [Compare Date] AND [Order Date] < DATEADD([pDatePartSelected], 1, [Compare Date]))

WHEN 3 THEN ([Order Date]>= DATEADD([pDatePartSelected],1,[Compare Date]) AND [Order Date] < DATEADD([pDatePartSelected],2,[Compare Date]))

WHEN 4 THEN ([Order Date]> DATEADD([pDatePartSelected],-1*[pSelectPeriods],[Compare Date]) AND [Order Date]<= [Compare Date])

WHEN 5 THEN ([Order Date] >= [Compare Date] AND [Order Date]< DATEADD([pDatePartSelected],[pSelectPeriods],[Compare Date]))

WHEN 6 THEN ([Order Date] >= [Compare Date] AND [Order Date]<= [pAnchorDate])
ELSE FALSE
END

This returns a boolean true or false. Add this field to the Filter shelf of the line chart and set to True.

If you show all the parameters on the view, you can play around manually altering the values to see how the line chart changes

Building the data range display

On a new sheet, add Dates To Show to the Filter shelf and set to True. Then add Order Date as a discrete exact date (blue pill) to Text and change the aggregation to Min. Repeat adding another instance of Order Date and change the aggregation to Max. Format the text accordingly.

Note at this point, I have reset everything back to be based on the original defaults I set in all the parameters.

Finally, there’s one additional sheet to build – something to display the ‘value’ of the option selected in the relative date filter control.

On a new sheet, using the Relative Options datasource, add Selected Relative Date Option to Filter and set to True. Then add Option Label to Text. Add row and column dividers, so it looks like a box.

Putting it all together

On a dashboard, add the Chart sheet (without title). Position the pDateLevel parameter above the chart, and add the input display box we built last, next to it (remove the title). Add a title to the whole dashboard at the top. Then remove the container that contains all the other parameters etc which don’t want to display.

You should have something similar to the below.

Change the title of the pDateLevel parameter and then adjust the input box so it looks to be in line – this can be fiddly, and there’s no guarantee that it’ll be aligned when it gets published to server. You may have to adjust again once published. You can try using padding – ultimately this is a bit of trial and error.

Now add a floating vertical container onto the sheet (click the Floating button at the bottom of the Dashboard panel on the left and drag a vertical object onto the canvas). Resize and position where you think it should be. Add a dark border to the container and set the background to white.

Now click the Tiled button at the bottom of the dashboard panel on the left, and add the Date Part Selector sheet into the container. Remove the title, and also remove any legend that automatically gets added.

Now add a line : add a blank object beneath the date part selector. Edit the outer padding of this object to be 10 px on the left and right and 0 px on the top and bottom. Change the background colour to grey, then edit the height of the object to 1px.

Now add the radio button chart beneath the line (fit entire view)., and again delete any additional legend, parameters added

Now add a Horizontal container beneath the radio button chart. Then click on the context menu of the radio button chart and select parameter > pAnchorDate to display the anchor date on the sheet. Hopefully this should land in the horizontal container, but if it doesn’t, you’ll have to move it. Add the pSelectPeriods parameter this way too.

Change the titles of these parameters as required, then add another line blank object using the technique described above, although delay changing the height for now.

Add the Date range sheet below, and fit to entire view, and now you can edit the height of the line blank object to size 1px.

Next select the container object (it will have a blue border when selected like below) and use the context menu to select Add Show/Hide Button

This will generate a little X show/hide floating object. Use the context menu of this object to Edit Button

I used powerpoint to create the up/down triangle images, which I then saved and referenced via the ‘choose an image’ option

I then added a border to this option and positioned it (through trial and error) where I wanted it to be.

Adding the interactivity

The final step is to now add all the required dashboard action controls.

At least, this is the final step based on how I’ve authored this post. In reality, when I built this originally, I added the objects to the dashboard quite early to test the functionality in the various calculated fields I’d written, so it’s possible you may have already done some of this already 🙂

We need to set which date part has been selected on the control. This is a parameter action

Set Date Part

On select of the Date Part Selector sheet, set the pDatePartSelected parameter, passing through the value of the associated Datepart Lower field.

Add another parameter to capture which radio button has been selected

Select Relative Date Option

On select of the Relative Date Selector sheet, set the pOptionSelected parameter, passing through the value of the associated Option ID field (ensuring aggregation is set to None).

Then we need to add dashboard filter actions to stop the selected options from being highlighted when clicked. Create a filter action

Deselect Dale Part Selecteor

on select of the Date Part Selector view on the dashboard, target the Date Part Selector sheet directly, passing the fields True = False, and show all values when the selection is cleared

Apply a second filter action using the same principals but with the Relative Date radio button selector sheet instead.

And with all that, the dashboard should now be built and functioning as required.

My published solution is here.

Happy vizzin’!

Donna