Incremental Refresh: Dates Earlier than 'RangeStart' once Published

[pmaviles]

Let me start by saying I'm new to Power BI. I've been reading up on and watching Youtube videos about incremental refresh.

I've set up the RangeStart and RangeEnd parameters and set up the actual incremental refresh: I'm wanting to keep any data within the last year but refresh records within the last two months. In the desktop version it seems to be working fine, card screenshot below confirming the earliest date is a year ago today.

However, when I publish to the web app the dates seem to mess up. I've published twice, trying different methods with the refresh:

First Method

Ideally, I want to have the "Get the latest data in real time with DirectQuery..." option selected but when this refreshed the earliest date changes to 02/01/2021.

Second Method

Same as above except I have the "Get the latest data..." or "Only refresh complete months" option deselected. Doing this I get the earliest date of 03/01/2021. This one doesn't make sense to me because, again, the earliest date should be 03/16/2021 since the refresh policy should only keep records for a year and we're not restricting it to refresh complete months.

I'm at a complete loss after Googling this for a few days. Any thoughts/suggestions are much appreciated.

[v-kkf-msft]

Hi @pmaviles ,

1. When initially configuring incremental refresh in Power BI Desktop, the parameters (RangeStart and RangeEnd) are used to filter only a small period of data to be loaded into the model. When published to the Service, with the first refresh operation, the Service creates incremental refresh and historical partitions and optionally a real-time DirectQuery partition based on incremental refresh policy settings, and then overrides the parameter values to filter and query data for each partition based on date/time values for each row. That is, the date range displayed after refreshing on Service is independent of the parameter value.

2. Partitions are created for and named by period granularity: Years, quarters, months, and days. Historical partitions contain rows by complete period up to the refresh period. That is, if you select the history period as "12 Months", then the minimum granularity of the history partition is "month". So the earliest date is "2021-2-1".

Table partitions can be seen using Tabular Editor.

For more information, please refer to: https://docs.microsoft.com/power-bi/connect-data/incremental-refresh-xmla#partitions 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

You are doing too many things at the same time.

First of all - the values of RangeStart and RangeEnd in the desktop have absolutely no impact on incremental refresh. They are only placeholders that help you - the developer - to limit the data you develop with. 

Second - in your Power Query make sure that both parameters are included in your query filter but only one of them can be inclusive, the other one must be exclusive

Third - remove all the extra options and only focus on getting incremental refresh to work

Fourth - Every time you make structural changes to your report and publish it to the workspace your incremental refresh will have to restart. The partitions are only recreated during the next refresh.

Fifth - the only sure way to know that your incremental refresh is working correctly is to observe the refresh timestamps of the partitions.  If you only see one partition then incremental refresh is not working.

[marukosu]

When I publish the report on service, and refresh the model, only data within the interval between RangeStart and RangeEnd continues to come.

Results on desktop:

Results on service after a five minutes refresh:

It's a ODBC query. I followed this video's instructions https://www.youtube.com/watch?v=x7q1DHf8wE4 and injected the date directly on query.

I carried out the same procedure in another report, with the difference that there the RangeStart was a much earlier date (01/01/2023). It brought the "historical data", due to this RangeStart, and then the incremental updates are working, as the time taken is very low.

Considering the settings below, in the first refresh to the Service, shouldn't the data already appear between 01/01/2019 and 05/30/2024?

[lbendlin]

@marukosu please open a new thread. Please show your Power Query code.