We've added the ability to filter dates by alternative columns to the BI-connector. In the advanced editor, you can select a date range for the report and add a filtering option: start date, modify date and end date.

In this way, the report will contain already filtered data and you won't have to configure additional sampling in Power BI.

How to filter data

For example, you have created a report on deals and want to see only closed deals for a certain period. To do this, go to Transform data section and open bx24_load_entity in the Advanced Editor.

Replace the code in the editor with a new one.

 Content = Json.FromValue([dateRange = [

                            startDate = #date(2022, 10, 23),
                            endDate = #date(2022, 10, 23)

                            //startDate = Date.AddYears(Date.From(DateTime.LocalNow()),-10),
                            //endDate = Date.From(DateTime.LocalNow())
                            
                            ],
                            key = #"Secret key",
                            configParams = [ timeFilterColumn = "DATE_CLOSED" ]
                            //DATE_CREATE (23.10.2022)
                            //DATE_MODIFY (23.10.2022)
                            //DATE_CLOSED (23.10.2022)
                        ]
                    )
                ]
            ),
            jd = Json.Document(response)

• startDate and endDate – specify the time interval of the data.


• configParams = [ timeFilterColumn = "*****" ] – enter the parameter by which the data will be filtered. You can specify the date of creation, modification or closing.

In our example, we have filtered the deals by the end date of 23.10.2022. This data can be used to build a report on closed deals.