How to set up incremental refresh for 2 years?

[Uldevi]

 

We have data for both past and future dates, with a systimestamp column that records updates or insertions daily. The data starts from 1/1/2021 and keeps updating every day. I would like to set up incremental refresh using the systimestamp column, as it is the only column available to identify updates.

I have created  RangeStart and RangeEnd parameters and filtered the systimestamp column. When I configured incremental refresh for 1 year, the range displayed was 1/1/2025 to 12/31/2025. When I set it to 2 years, the range displayed was 1/1/2024 to 12/31/2025. However, with 2 years selected, the data is incorrect, and the incremental refresh is not working as expected.

Could you please guide me on how to achieve this using incremental refresh?

[ajohnso2 · Accepted solution]

Is your data filtered on some kind of action date (within power query where you set range end/start)?

When you setup incremental refresh the table within SSAS will have a number of auto partitions created which will correspond to the action date (range end/start filter).

Using sysdate should work to only pick up new data (this is optional and should be seperate of your action date). If your using sysdate for your range start/end the partitions will be based on this date and not match the date of your actual fact data.

Since you mentioned you have future dated data I dont think you can use the out the box incremental 'wizard' I have setup something similar (budgets/forecasts) the only way I could include future dates was to create a powershell script api which modifies the 'refresh date'

https://community.fabric.microsoft.com/t5/Desktop/Advanced-incremental-refresh-via-API-All-OTHER-tab... 

[audreygerred]

When you say the incremental refresh is not working as expected, what is it doing? You could also just do 4 quarters or 12 months. And, the detecting data changes is optional, so you can just turn that part off.

[Uldevi]

Thanks,

I want to enable the "Detect Data Changes" option as I would like to update or insert data only where changes are detected, using SYSTIMESTAMP.

Is it fine if I perform incremental refresh twice a week, and does Power BI have any time limit for incremental refresh as I wanted set for 2 years or 1 year.