Sometimes it is necessary to have a dynamic threshold based on a date. For example,  you may want to have a bar chart showing the current years data and you want to show the bars that are beyond the current date in a different colour to those prior to the current date.

Look no further as the answer you have been waiting for is below.

Step 1:

Create a calculated measure with a statement that is like the one below.

If([Date].[h/YMD] > [@Current Date Calculation Input],1+[Amount],[Amount])

This will require the creation of a new calculation Input control.

Under the Dimension option you should select the Date dimension as shown below.

Next select Range under the Values dropdown

This will bring up the Set Date Range window. From here you can choose the hierarchy that you want. Note: that you will need to select the same Date Hierarchy as you are using in the formula above

You can also choose the granularity at which you want to report it (Month, Day etc…). In the example below we have selected to go down to a Day granularity.

Note: under Setting for Users you will need to change the selection to single selection (it will default to multiple selection and this will cause an error in the formula.

Step 2:

In the above example we left the Current Date selection as System Date / Period. To satisfy the requirements of this example I need something a bit more dynamic. To achieve this, I have added a Current Date Input Control as can be seen below. To create the input control select the drop down below Current Date and select Create Current Date Input Control.

This will bring up a new window that will allow you to change the granularity, name and default date.

This will create a control for the Story that allows the user to set the “Current Date” for the entire Story, this can then be used to filter and control the date in various parts of the Story.

Once you are happy with your selection criteria click OK on both windows. (Set Date Range and Calculation Input Control). You will now see your Calculation Input Control under Available Objects.

Step 3:

Now back to the formula you created earlier.

If([Date].[h/YMD] > [@Current Date Calculation Input],1+[Amount],[Amount])

What this is doing is comparing the Date dimension in the Chart to the Current Date in the Current Date Calculation Date Input. If the Date is higher than the Current Date then it adds 1 to our measure. If it isn’t (i.e., it is before the value entered in the Current Date) then it is just the Amount Value.

This calculation will ensure that for every data point (be it Day, Month, Quarter etc…), if the date is past the Current Date Input Control value then the Amount will be 1 higher than the actual amount. And for every date before it will be the same.

As you can see above our Flag Measure (shown in red) is 1 higher than our Amount on all dates beyond the Date set by Current Date Input Control (Jan 2022 in this example). When we change the value of our Current Date Input Control it will also change reflect this through in the graph.

Step 4:

Now we have setup our Flag Measure and proved that it is working we can move on to creating our threshold.

By selecting Add and then Threshold this will bring up our Threshold panel as shown below. Withing this panel we select the measure that we want to display in the Measure drop down. By default, the Compare To selection is set to Number Range and we should change this to Measure. For our Comparison Measure we select the calculated measure you created and in this example, it is called Flag Measure.

The final step is to edit the threshold ranges and colours as shown below and hit Apply.

Note: sometimes nothing will happen and you might need to go to the Amount in your Builder panel and manually set the Threshold as shown below.

Step 5:

Now your chart should look something like the one below.

Now for a bit of an explanation of what is happening with the Threshold Ranges we created.

What we are doing is comparing the value of our Amount to the value of our Flag Measure. There are two possibilities here:

1. The date is after the selected date in which case The Flag Measure is 1 higher than the Amount. In this case when we compare the Amount to the Flag Measure it will be less than 100% of the Flag Measure therefore the bars are shown in green.
2. The date is on or before the selected date. The Flag Measure and the Amount equal each other. As such when the Threshold compares our Amount to our Flag Measure it will be 100% of it therefore the bars are shown in red.

I hope this all makes sense and is useful to you. Please look out for my next blog on How to Date with SAC.