In this blog, I will be showing how to create a dynamic reference line with a parameter action. I will also show how to colour anything before the reference line a different colour to what is after it.

We will be using the Superstore dataset for this tutorial.

Step 1

Drag Order Date to columns and set it to Exact Date, and drag Sales to rows. You will then see the above chart.

Step 2

Now we can create our parameter that will be used later on. I named mine “Date Parameter”, set the Data Type to Date and set the current value to anything within the range of Order Date.

Step 3

After that, we can create our reference line. For the scope, I selected Per Pane, as well as setting the value as our newly created parameter. I also set the label as Value so that we can see the date value next to the line itself.

After some formatting of the line and adjusting where the label appears in the format options, here is what our graph should look like so far:

Step 4

We can now add a Parameter Action to our worksheet. If you click Worksheet > Actions > Add Action > Change Parameter, you will see a window that looks like this:

After naming your Parameter Action, select the source sheet (the current sheet you are working on) and Select for the “Run action on” section. Then for the Parameter section at the bottom, select the parameter we created earlier. For the field, this will be Order Date. Leave “Aggregation” as None and “Clearing the selection will” as Keep current value.

You will now be able to click anywhere on the chart and the reference line will also jump to that point! Here is how it should look:

Step 5

We’re almost done! Now we just have to add a calculated field to change the colour of the chart before and after the line:

[Order Date] < [Date Parameter]

Drag this new calculated field to Color on the Marks card and change the colours to whatever you like.

But wait!

You may have noticed that when you click around on the chart, parts of it become “greyed out”. We can solve this by creating a dummy calculated field with anything you want inside:

Now we can create a new Filter Action – I named mine “Dummy Action” for this example. Select your worksheet in the “Source Sheets”, as well as “Select” for the “Run action on” section. The target sheet in this case will be the same as the source sheet. Leave “Clearing the selection will” as “Show all values”.

Select “Selected Field” under “Target Filters”, followed by “Add Filter”. In the new pop up window that appears, ensure your data source is selected along with your “Dummy” calculated field. For the field in the “Target” section, simply choose any field that is not being used.

And we’re done!