By default, Power BI incremental refresh partitions only include data up to the current period, leaving future-dated entries excluded. This can be problematic for scenarios requiring forward-looking data. In this article, we demonstrate how to modify the partitioning logic to include future dates within the most recent partition. While this example focuses on day-level refresh partitions, the approach can be easily adapted for year or quarter granularities.
The Goal: Capture Future Data
In incremental refresh, this would happen by default when there are dates in the future:
| Period | Data Location |
| Dates in the Past | Archived Partitions |
| Dates until Current Date | Refreshed Partitions |
| Dates in the Future | Data is Excluded or Lost |
This is what we will accomplish:
| Period | Data Location |
| Dates in the Past | Archived Partitions |
| Dates until Current Date | Refreshed Partitions |
| Dates in the Future | Data in Last Partition |
Modifying the Filtering Expression
To include future-dated entries, we modify the filtering expression to identify the most recent partition dynamically, ensuring future dates are assigned to it.
In our example, the original filtering expression is as follows, where the date field is PLANNED_PACK_DATE:
[PLANNED_PACK_DATE] >= RangeStart
and [PLANNED_PACK_DATE] < RangeEnd
This expression includes data within the specified range (RangeStart to RangeEnd), which defines the partition scope.
Our modified filtering expression to cover future dates is this:
(
[PLANNED_PACK_DATE] >= RangeStart
and [PLANNED_PACK_DATE] < RangeEnd
)
or (
[PLANNED_PACK_DATE] > RangeStart
and RangeStart = DateTime.LocalNow()
)
The OR clause ensures future dates are included if they fall beyond RangeStart and the partition is the most recent one (RangeStart = DateTime.LocalNow()).
Does it Work?
To verify the updated partition logic, examine the partitions and their row counts in SQL Server Management Studio:
Next, we connect to the dataset and manually calculate the total row count for data starting from the current date onward. This count should align with the row count of the last partition. For this example, the current date is January 16, 2025, which corresponds to the last partition containing 2,746 rows. By applying a filter to include only dates on or after January 16, 2025, we verify the row count matches exactly at 2,746. This confirms the updated filtering logic is working as intended.
Conclusion
By modifying the filtering expression for incremental refresh, Power BI can dynamically include future-dated entries in the last partition. This approach ensures no data is excluded, even when dates extend beyond the current date. The technique is flexible and adaptable to various granularity levels, making it a valuable tool for managing datasets with forward-looking data.