Use date filters to select, analyze, and compare data. They help you track trends and avoid showing unnecessary data in dashboards.

This article explains how to test requests and apply date filters in BI Builder.

Test a request and check how filters work

Use Postman to test your request. Postman lets you send API requests and review responses.
Request debugging using Postman

If your dataset includes a date-time field, you can use it as a filter. For example:

• date_create
• date_modify
• begindate
• closedate

Description of entity fields in BI Analytics

Example: filter deals by creation date

1. Set the request method to POST.
2. Use this URL:
   https://account_name.bitrix24.com/bitrix/tools/biconnector/pbi.php?table=crm_deal

Add &explain to view the SQL execution plan.

Create a request to Bitrix24 with these three parameters:

• key: "vMYihzN3QS4XXXXXXXXXX": Your unique access key. Find it in CRM > Analytics > BI analytics > BI analytics settings > Manage keys.

• dateRange: {"StartDate": "2022-1-1", "EndDate": "2023-1-1"}: Set the time range for the data request. For example, from January 1, 2022 to January 1, 2023:

• configParams: {"timeFilterColumn": "DATE_CREATE"}: Specify the field used for date filtering.

Example request

 { "key": "vMYihzN3QS4XXXXXXXXXX", "dateRange": { "startDate": "2022-01-01", "endDate": "2023-01-01" }, "configParams": { "timeFilterColumn": "DATE_CREATE" } } 

Click Send.

To check the results in Bitrix24, go to CRM > Analytics > BI analytics > BI analytics settings > Usage. This page shows the usage statistics, data source, and filters applied to the dataset.

Add a date filter to a dashboard chart

1. Open your dashboard.
2. Click + Create a new chart.
3. Select the crm_deal dataset.
4. Choose a chart type.
5. Click Create new chart.

Configure the chart

1. Move Quantity to the Metric section.
2. In Filters, select a date field (for example, date_create).
3. Click Create chart.

Save the chart

1. Enter a name.
2. Select a dashboard.
3. Click Save and go to dashboard.

Add a time range filter

1. Click + Add/edit filters.
2. Select Time range.
3. Enter a name.
4. Click Save.

Set a time range, for example, Last month, then click Apply filters.

The chart updates and shows deals created during that period.

To verify filter details, go to CRM > Analytics > BI analytics > BI analytics settings > Usage.

Filter data in a virtual dataset

To filter data in a virtual dataset, update the SQL query.

1. Open the Datasets tab.
2. Select a virtual dataset.
3. Click Edit.

Here’s an example. We’ll edit a dataset that includes standard deal fields, custom deal fields, and linked product details. Click here to learn how to create such a dataset in BI Builder.

The SQL query does not include the date_create field yet. Add it, then save the updated query.

 SELECT d.TITLE, d.date_create, du.UF_CRM_1702288663142, dp.PRODUCT_NAME FROM crm_deal d INNER JOIN crm_deal_uf du ON d.ID = du.DEAL_ID INNER JOIN crm_deal_product_row dp ON d.ID = dp.DEAL_ID WHERE d.date_create >= DATE '2023-01-01' AND d.date_create < DATE '2023-12-31'; 

This query does the following:

• d.TITLE selects the deal name from crm_deal.

• d.date_create filters deals by the Created on field.

• du.UF_CRM_1702288663142 selects a custom field from crm_deal_uf.

• dp.PRODUCT_NAME selects the product name from crm_deal_product_row.

• INNER JOIN connects tables by the shared DEAL_ID.

• WHERE d.date_create filters deals created during the selected period.

To show the new field in the virtual dataset, open the Columns tab and click Sync columns from source.

Then replace the WHERE clause with a dynamic filter. This lets you filter data without editing the query each time.

 SELECT d.TITLE, d.date_create, du.UF_CRM_1702288663142, dp.PRODUCT_NAME FROM crm_deal d INNER JOIN crm_deal_uf du ON d.ID = du.DEAL_ID INNER JOIN crm_deal_product_row dp ON d.ID = dp.DEAL_ID WHERE {% if from_dttm is not none %} d.date_create >= from_iso8601_timestamp('{{ from_dttm }}') AND {% endif %} {% if to_dttm is not none %} d.date_create < from_iso8601_timestamp('{{ to_dttm }}') AND {% endif %} true; 

Create a new chart, add a field for filtering, and click Save. You can also add a column that shows when each deal was created.

The dashboard now shows deal data for the selected period.