Incremental Refresh

[danyzion]

Hi,

I have a power bi dashboard which pulls the data from my SQL query.  The query runs for data since 202301 which is taking a lot of time. I have created parameters for rangestart and range end and set my incremental refesh policy as refresh last 3 months data. 

Question:

* Since I have mentioned a range end in my query and assigned a range end parameter as say, 202405 will my dashboard always pull data only till 202405?

* We make some changes every now and then to the backend code, in this case how can we refresh the data/changes only for the latest months (say last 3 months) 

[rajendraongole1 · Accepted solution]

Hi @danyzion -Yes, if you've set the RangeStart and RangeEnd parameters in your SQL query and your Power BI incremental refresh policy to only refresh the last three months, your dashboard will adhere to these constraints

Data Range in SQL Query: Since your SQL query includes a filter up to RangeEnd (e.g., 202405), Power BI will not retrieve data beyond that date. It will pull only the records up to 202405, so you won’t get any data beyond this end date unless you manually update the RangeEnd parameter in your query.

With the incremental refresh policy set to refresh only the last three months, Power BI will refresh data only for the three most recent months within the range specified by your parameters. This means it will refresh records for 202403, 202404, and 202405 only.

So, if you want to extend the refresh window beyond 202405 in the future, you’ll need to update the RangeEnd parameter to reflect the new date range.

[Bibiano_Geraldo · Accepted solution]

Hi @danyzion, let’s address your questions one by one:

Range End Parameter:

If you set a RangeEnd parameter to a specific date, such as 202405, your dashboard will pull data only up to that date. This means that any data beyond 202405 will not be included in your dataset.

Refreshing Data for Latest Months:

When you make changes to the backend code and want to refresh only the latest months (e.g., the last 3 months), you can leverage the incremental refresh policy. Ensure that your incremental refresh policy is set correctly to refresh only the last 3 months. This way, even if you make changes to the backend code, only the data for the specified period will be refreshed.

Modifying Backend Query:

If you skip the RangeEnd and pull data since 202301, the incremental refresh will continue to refresh the latest 3 months of data. However, if you modify the backend query (e.g., adding or removing a column), Power BI will treat this as a structural change. In such cases, the entire dataset might need to be refreshed from the beginning (202301) to accommodate the changes.

You can learn more about incremental refresh Here 

[danyzion]

Also let's say I skip the end range and pull data since 202301. Now if incremental refresh would keep on refreshing the latest 3 months data, if I want to make changes to the backend query by adding or removing one column and then refresh the data, it would refresh again from 202301 is it? Since I have modified the code

[rajendraongole1]

Hi @danyzion - When you set the RangeEnd parameter to a specific date (e.g., 202405), Power BI will use this date as the upper boundary for your data pull. So if your SQL query is structured to use RangeStart and RangeEnd as filters, the data pull will be limited to the range between RangeStart and RangeEnd. This means:

Initial Load: Power BI will pull historical data until 202405 for the first time, storing it in the dataset.
Incremental Refresh: After that initial load, Power BI will use the refresh policy settings (e.g., "last 3 months") to only refresh data from the latest months. Power BI keeps the older data intact while only updating data from the last 3 months.
This approach will prevent Power BI from reloading the entire historical dataset each time, improving performance.

conclusion: configure with RangeEnd set to 202405, Power BI will restrict the data load up to this date.
For backend changes, if they only impact recent data, incremental refresh will handle the updates for the last 3 months. For structural or schema changes, you may need a full refresh once to update Power BI’s dataset schema.

[danyzion]

Thanks @rajendraongole1 and yes my SQL query has data filtered on start and end range. So if I have given my end range as 202405, there's no way I would be getting data post 202405 rather it will keep refreshing 20403,04,05 is it?

[rajendraongole1 · Accepted solution]

Hi @danyzion -Yes, if you've set the RangeStart and RangeEnd parameters in your SQL query and your Power BI incremental refresh policy to only refresh the last three months, your dashboard will adhere to these constraints

Data Range in SQL Query: Since your SQL query includes a filter up to RangeEnd (e.g., 202405), Power BI will not retrieve data beyond that date. It will pull only the records up to 202405, so you won’t get any data beyond this end date unless you manually update the RangeEnd parameter in your query.

With the incremental refresh policy set to refresh only the last three months, Power BI will refresh data only for the three most recent months within the range specified by your parameters. This means it will refresh records for 202403, 202404, and 202405 only.

So, if you want to extend the refresh window beyond 202405 in the future, you’ll need to update the RangeEnd parameter to reflect the new date range.