I have attached an example for you.
To add your quick filter to the dashboard, you just need to create a new dashboard. You can select the single value list option to get radio buttons.
Also, the forecast option was grayed out because you were not using a continuous date format, and the table calculation you were using didn't support forecasting.
I have included both a forecast example and the radio button filter in the attached file.
Let me know if you have questions.
DateExample.twbx 20.1 KB
When I look at Dashbaord 1 in your example, instead of having options that say (All), False, and True, can the options just say "True" and "Forecast"? "True" would act what True currently does and Forecast would act as what (All) currently does.
I set the calculated field to iif([Opened]<= TODAY(), "True", "Forecast").
When I select Forecast it removes the data from 2015. If I select (All) it keeps it. How can I keep the 2015 data if I select Forecast?
Filtering won't work for this, parameters will have to be used instead - unless you're happy keeping the 'All' and losing the Forecast of course, in which case filtering is fine.
Create a parameter called 'Selection' with the values True and Forecast.
Alter the calculated field 'Months to Date' to be 'IF [Opened]<= TODAY() then "True" end'
Create a new calculated field called Filter:
IF [Selection] = "Forecast" THEN 'True'
ELSEIF [Selection] = [Months to Date] THEN 'True'
Selecting Forecast in the parameter actually selects all dates. Selecting True selects only those months with a value of True, set in the Months to Date calculated field.
I've attached the twbx.
DateExample.twbx 27.6 KB
Thanks that looks like it will work great. Is it possible to have the Mark type to be a dotted line for any future months?
It's possible to show as a different colour but I'm not sure about dotted line - this I complicated. If you want to explore dotted lines further you can check this out: Eddie Van Halen and Dashed Lines | Drawing with Numbers
To do a different colour it would take a slightly different approach than we looked at previously but is significantly easier than a dotted line. Based on my understanding you want:
- Anything 'actual' - i.e. before today - to be displayed always
- Anything 'forecast' to be displayed when the user wants to see it
In which case you can produce a calculated field to show 'actual', a calculated field such as IF [date] <= today() THEN [Measure] END.
Create a very similar calculated field to show 'forecast', such as IF [date] > today() THEN [Measure] END.
Put these on to a dual axis line chart and Synchronise the axes.
You should now see the Actual and Forecast appearing like a line chart, with a break to split the Actual and Forecast.
Next is to show or hide the Forecast depending on the user choice. Create a new calculated field, ShowForecast, IF [Selection] = 'Forecast' THEN [Forecast] END. [Selection] is the parameter with values of Forecast or Actual.
Put ShowForecast in the place of the Forecast pill on the chart and now you can switch the forecast on and off plus easily differentiate which is forecast and which is actual.
Attached is a workbook I quickly threw together to show you how it works.
HideForecast.twbx 21.4 KB