Forum Discussion

askme1217's avatar
askme1217
Frequent Visitor
11 months ago
Solved

Incremental refresh doubt

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 ...
Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    11 months ago

    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.

Poojara_D12's avatar
Poojara_D12
Super User
11 months ago

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.