Incremental Refresh is not working when parameters are set

[aume1]

Hi, 

I have a report that has Incremental Refresh (henceforth IR) applied to it. The purpose of it is to let the report store the past 10 months of date + current month while only updating the latest 10 days. So my set up looks like this:

The report is refresh daily, with the most up-to-date data from 2 days ago (so today being 13th Nov, the report should contain all the data up to 11th Nov). I have created the parameters RangeStart and RangeEnd on 12th Nov to take in consideration of this date, so I have RangeStart set to 10th March 2021 and RangeEnd to 11th Nov 2021 (since the filter is [Date] >= Range Start and [Date] <  RangeEnd, this should return 10th Nov as the latest date). After the first scheduled refresh, however, the maximum date in the report is still set to 10th Nov 2021, which should be 11th Nov 2021 if IR is working correctly. 

The IR works if I set the RangeEnd to far in the future (like 01/01/2050) but I've read that once the report is published to the service, this RangeStart and RangeEnd should get overwritten as the days go on. So what really is the point of setting it precisely to the date range of partition, and what could be the reason behind the dates not being updated when the parameters are set as above?

Apologies if a similar question is asked and solved in the past.

[lbendlin]

Two comments:

- The values of RangeStart and RangeEnd that you specify in Power Query have nothing to do with the actual workings of the Incremental refresh.  They are placeholders that you can use to limit the amout of data to use for development (if you so wish)

- You have "Detect data changes" enabled but the field is blacked out. This field must be different from the field you use with the RangeStart/RangeEnd filters.  It also has a massive impact on the way Incremental Refresh works.

[aume1]

Thanks for the reply. 

Detect data changes is enabled with a column in the table which is updated daily to record when the new rows were inserted to the table, which I assume would be picked up by PBI service to only refresh the newly updated rows in the table within the defined refresh period of 10 days. Is this a correct assumption on this feature?

[lbendlin]

Yes, that is correct for scenarios where rows get added. It will not help like this for rows that are changed or deleted.  It will also massively increase the required space as it creates a second copy of your partitions ("canary") to do its thing.

[v-henryk-mstf]

Hi @aume1 ,

It looks like the problem may be caused by a time zone conflict. Troubleshooting

Try again and give me feedback.  Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aume1]

Thanks for your reply. I do not think the time zone conflict is an issue as it is set to the time zone where I live and scheduled refresh is set accordingly. 

The refersh now works with RangeEnd set to a date in 2025, but I have a question on how the "Store Columns in the last..." and "Refresh rows in the last...". From the documentation, I understand that "Store..." works in a way that it loads the speicified complete months (10 months, in this case) plus the current months. My question is, once this month is complete, does PBI Service delete the entire month from the Historical Period once next month starts? My current set up is 10 months from March 2021, so once Feb 2022 starts, is the entire March 2021 data removed?

[lbendlin]

Sort of.  In reality the process is a bit more intricate, and depends on your chosen granularity for the partitions.

Advanced incremental refresh with the XMLA endpoint in Power BI - Power BI | Microsoft Docs