Incremental refresh in Power BI typically implements a rolling window for dates and data, as shown in this image from the official documentation:

But what if you need to archive data starting from a specific fixed date? In other words, instead of a rolling window, you want the window to expand over time while preserving all data from a certain date onward. For instance, you might want to always retain data from 1/1/2019 onward. This means yearly archived partitions should be added (e.g., 2025, 2026) but never remove earlier years like 2019 or 2020.

Solution: Archiving with a Fixed Start Date

1. Set a Large Archive Window
   To ensure early data (e.g., 2019) isn’t removed, define a large number of years for the archive window. For example, if you set a 20-year archive, the earliest partition would start from 2006 (assuming the current year is 2025). This guarantees that the 2019 partition won’t be overwritten until around 2038. If needed, you can extend the archive window further.
   
2. Exclude Unwanted Partitions
   To avoid loading data for years prior to your fixed start date (e.g., 2006–2018), modify the filtering step in your query to exclude irrelevant data. Here’s how:

Original filter:
[CREATE_DATE] > RangeStart
and [CREATE_DATE] <= RangeEnd

Updated filter:
[CREATE_DATE] > RangeStart
and [CREATE_DATE] <= RangeEnd
and [CREATE_DATE] >= FixedStartDate

Replace FixedStartDate with your fixed date (e.g., 1/1/2019).

By applying this filter, partitions for 2006–2018 will still be created, but they will remain empty.

Key Considerations

• Manage Performance: Be mindful that defining a large archive window increases the number of partitions created, which could impact performance.
• Future Adjustments: Periodically review your archiving strategy to ensure it aligns with your data retention policies and storage capabilities.

This approach allows you to maintain a growing archive while preserving all data from your fixed start date onward.