Overview:

We can filtering data to show information from the past five years for a specific date can be achieved in Bold BI^® by using a custom SQL query. This guide provides a detailed step-by-step process to set up this functionality using a date picker widget.

Steps to Filter Data from the Past 5 Years:

Step 1: Create a Data Source and Import Dataset

Begin by creating a data source, which could be from various sources such as Excel, a database, etc. Import your dataset into Bold BI^® and save it as “Datasource1”. Within Datasource1, create a dashboard parameter, which will be a date parameter that users can select or input to define the specific date for which the past five years of data is needed.

Step 2: Access the Underlying Query

After creating the parameter in Bold BI^®, switch to the code view mode to access the underlying query of the data source. Copy this query as it will be used as a base for the next data source.

Step 3: Create a New Data Source with Modified Query

Create a new data source named “Datasource2”. Use the query copied from “Datasource1” and modify it by adding a WHERE condition that filters data from the past five years based on the dashboard parameter.

SELECT
    [Sheet1].[Date] AS [Date],
    [Sheet1].[Type] AS [Type],
    [Sheet1].[Value] AS [Value]
FROM [Datasource].[Sheet1] AS [Sheet1]
WHERE
    CONCAT(DATEPART(DAY,[Sheet1].[Date]),'-',DATEPART(MONTH,[Sheet1].[Date])) = CONCAT(DATEPART(DAY, CAST(@{{:Datasource1.Parameter1}} AS Date)),'-',DATEPART(MONTH, CAST(@{{:Datasource1.Parameter1}} AS Date)))
    AND YEAR([Sheet1].[Date]) BETWEEN YEAR(GETDATE()) - 4 AND YEAR(GETDATE())

Step 4: Bind the Data Source to Your Dashboard

Save “Datasource2” with the modified query. Bind this data source to your dashboard, ensuring that the data displayed is filtered based on the parameter. Use a widget, such as a date picker, where the user can select the specific date. Drag and drop the dashboard parameter column into the assign data section of the date picker widget to control the filtering.

Step 5: Test the Filter

Test the filter by selecting a date in the date picker widget and verifying that the dashboard updates to show data from the past five years relative to the selected date.

Additional References:

• Bold BI^® Documentation: https://support.boldbi.com/documentation/
• SQL Server DATEPART Function: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql
• SQL Server GETDATE Function: https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql