Date filters on the dashboard help select and analyze data for specific periods. Properly configured filters in an SQL query can speed up dashboard performance and limit data selection, which helps prevent exceeding BI Builder limits.
Bitrix24 plans: Rows per data table

Filtering data in a query

Query filters help extract only the information you need from Bitrix24. For example, view how many deals appeared over two years without loading all the data at once.

To add a date range to your query:

1. Open BI Builder and go to SQL > SQL Lab.
2. Select the bitrix24 schema.
3. Enter the SQL query, add a date filter, and click Run.

BI Builder will run the query with the filter, returning data only for the selected period. This improves dashboard performance and helps avoid exceeding limits. You can save the completed query as a dataset to create a chart for the dashboard.

If you're using a filter for the first time, refer to the sample query.

SELECT
    TITLE,
    DATE_CREATE
FROM crm_deal
WHERE
    DATE_CREATE >= TIMESTAMP '2023-01-01 00:00:00'
    AND DATE_CREATE <= TIMESTAMP '2024-12-31 20:00:00';

Query usage statistics in BI Builder

Dashboard charts are created using SQL queries. The faster the queries run, the quicker the dashboards load in Bitrix24. By analyzing statistics, you can evaluate query performance. If queries are slow or load too much data, make adjustments to improve dashboard speed.

To view query usage statistics in BI Builder, go to BI Builder > Analytics hub > Usage statistics.

If you create dashboards or add new charts, we recommend regularly checking the statistics. The table shows query results, including the number of rows extracted, applied filters, and data volume loaded.

For example, a query to extract deals loaded 1,036 rows and 32,312 bytes of data. The execution time was 0.77 seconds, which is slower than other queries. The statistics show that no filters were applied, which may have slowed down the dashboard.

To speed up query execution, you can add a filter by creation date. This will reduce the data volume and speed up the report. For instance, if you only need deals from the last year, add this condition to the SQL query:

WHERE DATE_CREATE >= TIMESTAMP '2024-01-01 00:00:00'

In the Usage statistics section, you can check how the query performance has changed.

The filter reduced query time, rows, and data volume.

Date range filters in BI Builder

In BI Builder, you can use various filter options to optimize queries and improve dashboard performance. Let's explore three main date range filters and how they impact data selection.

Single date range. This filter selects data created within a specific time frame. It's useful for seeing how many deals appeared over two years without loading all the data at once.

SELECT /* Fields from the dataset */
FROM /* Dataset name */
WHERE
    DATE_CREATE > TIMESTAMP '2023-01-01 00:00:00'
    AND DATE_CREATE < TIMESTAMP '2024-12-31 23:59:59';

The filter quickly selects deals by the DATE_CREATE field, loading only the necessary data to speed up query execution. In the Usage statistics section, it appears as: {">=DATE_CREATE":"01\/01\/2023","<=DATE_CREATE":"12\/31\/2024 11:59:59 pm"}.

Overlapping date ranges. This filter selects data for two overlapping periods. It's useful for comparing two periods and considering overlapping data. For example, analyze deals for 2023 and from June 2023 to June 2025. The filter will select all deals created within these periods, including those that overlap.

SELECT /* Fields from the dataset */
FROM /* Dataset name */
WHERE
    (DATE_CREATE > TIMESTAMP '2023-01-01 00:00:00'
     AND DATE_CREATE < TIMESTAMP '2024-12-31 23:59:59')
OR
    (DATE_CREATE > TIMESTAMP '2023-06-01 00:00:00'
     AND DATE_CREATE < TIMESTAMP '2025-06-30 23:59:59');

The filter combines the two periods into one overall range during query processing. This can increase the data volume. In the Usage statistics section, it appears as: {">=DATE_CREATE":"01\/01\/2023","<=DATE_CREATE":"06\/30\/2025 11:59:59 pm"}.

Two-date range filter. This filter selects data based on two date fields: DATE_CREATE and DATE_MODIFY. It's used to analyze deals created and modified within a specific timeframe.

SELECT /* Fields from the dataset */
FROM /* Dataset name */
WHERE
    DATE_MODIFY > TIMESTAMP '2023-01-01 00:00:00'
    AND DATE_MODIFY < TIMESTAMP '2024-12-31 23:59:59'
    AND DATE_CREATE > TIMESTAMP '2023-01-01 00:00:00'
    AND DATE_CREATE < TIMESTAMP '2024-12-31 23:59:59';

Filtering is done by the first date specified. To filter by another date, change the order of conditions in WHERE. In the Usage statistics section, it appears as: {">=DATE_MODIFY":"01\/01\/2023","<=DATE_MODIFY":"12\/31\/2024 11:59:59 pm"}.