Incremental refresh doubt

[askme1217]

in my fact table, datetime column is not daily but yearly like start of year 01/01/2021 00:00:000, 01/01/2022 00:00:000  etc.  In the incremental refresh policy, i set it up to archive starting last 10 years of data and refresh last 2 years incrementally. So my question, In my case, since date column is not daily, how does it refresh ?   if i refresh on 10/01/2026, i need last 2 years, so it takes last 2 years from the date of refresh ? doesit consider years 2026 and 2025 only? 

[v-tejrama]

Hi @askme1217 ,The datetime column in the fact table only has 01/01/YYYY values, but incremental refresh uses date ranges. So, when refreshing for the last 2 years, data for years like 2024 might be missed because only the start of each year is present.

Solution:

Add a Year column: Create YearKey = YEAR([DateColumn]).

Set up incremental refresh using YearKey, targeting the last 2 calendar years.

If daily datetime is required, consider adding a dummy column with 01/01/[Year] to support filtering.

This approach ensures all relevant yearly records are included in the refresh.

It prevents data from being missed due to differences in date granularity.

Incremental refresh becomes more reliable for yearly data structures.
Review and update existing partitions as needed to align with the new YearKey filter.

[v-dineshya]

Hi @askme1217 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,

Dinesh

[v-tejrama]

Hi  @askme1217 ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[v-tejrama]

Hi  @askme1217 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Poojara_D12]

Hi @askme1217 

Incremental refresh in Power BI is always driven by the column you configure for the policy (your yearly datetime column) and the current refresh date/time when the model is processed. When you set it up with store the last 10 years and refresh the last 2 years, Power BI evaluates that logic relative to the refresh date.

So if you refresh on 10-Jan-2026, Power BI calculates:

Archive window → everything from 10-Jan-2016 up to the beginning of the refresh window.

Refresh window → everything from 10-Jan-2024 to 10-Jan-2026 (last 2 years).

Now, because your fact table’s datetime values are only stored at the year level (e.g., 01-Jan-2021, 01-Jan-2022, etc.), Power BI compares those year-level timestamps against the window boundaries. This means:

01-Jan-2026 (the record for 2026) is included in the refresh window.

01-Jan-2025 is also included in the refresh window.

01-Jan-2024 is excluded, because it falls just outside the last 2 years when compared against 10-Jan-2026.

 So yes, in your setup the refresh window will only bring in 2026 and 2025 data during that run. The archive window continues to hold everything from 2016 through 2024 without being reprocessed.

This means your fact table is always refreshed at the year grain, not at the daily level — which is fine as long as that’s what your data source provides.

[FarhanJeelani · Accepted solution]

Hi @askme1217 ,

incremental refresh uses the date column and the RangeStart/RangeEnd window, not calendar days. With yearly dates, you’ll still get partitions for the years that fall into the incremental window.

In your scenario:

Refresh date: 10/01/2026
You configured: Archive last 10 years, refresh last 2 years incrementally
The incremental window = [RangeEnd - 2 years, RangeEnd] = roughly [2024-10-01, 2026-10-01]
With yearly dates (01/01/YYYY):
The partitions that exist in that window are typically 2025-01-01 and 2026-01-01 (if those rows exist in your fact table).
So you will refresh those two yearly partitions (2025 and 2026), and older years (2021–2024) are archived unless their dates fall into the window.

Note: 

If there is no data for 2026-01-01 yet, that year won’t be refreshed even if you expect it to.
The window is always calculated at refresh time (RangeEnd is the refresh time). It doesn’t look back “two calendar years from today” independent of data presence.
For predictable behavior, it’s usually best to have a daily-granularity date column (a proper calendar table) used for the incremental range, even if your fact table only contains year-start dates. That helps Power BI fold the query cleanly and makes the window behavior consistent.

Please mark this post as solution if it helps you. Appreciate Kudos.

[ryan_mayu]

@askme1217 

With your current setup, the incremental refresh will only refresh the last 2 years (e.g., 2025 and 2026 if refreshing in October 2026), because those are the only dates in your fact table that fall within the last 2-year window from the refresh date.

[NimaiAhluwalia]

Hello @askme1217 

Power BI checks your filter column (the yearly datetime) relative to the refresh date:

• "Archive 10 years" → keeps data from 2016–2026.

• "Refresh last 2 years" → refreshes data for 2025 and 2026.

• Older partitions (2016–2024) remain as-is (no reprocessing).

 Even though your column is year-level, Power BI still compares the values to the refresh date and applies the rules.

So yes only 2025 and 2026 will be refreshed when you run on 10-Jan-2026, because your incremental refresh policy is based on the refresh date, not just the values in the column