Forum Discussion
Why does Incremental Refresh require DateTime when data contains only date?
Power BI best practices recommend separating DateTime into Date and Time columns to reduce cardinality and improve performance. However, when configuring Incremental Refresh, it forces you to use a column with DateTime data type even if your dataset only contains dates and no time component.
This seems contradictory, especially since using DateTime unnecessarily increases cardinality and can slow performance. Even converting a pure Date column to DateTime (by appending midnight timestamps) feels like an artificial workaround.
Is there a recommended approach to balance both needs? Why can't Incremental Refresh support Date types directly when no time values are present?
The best practice recommendation is valid for incremental refresh setups as well, guaranteeing that all time values inside a date will sit in the same partition. You can do the conversion at the last minute, via
let d = Datetime.From([Date value]) in d >= RangeStart and d < RangeEnd
15 Replies
Incremental Refresh expects a DateTime value that is usually pegged at midnight UTC, or a date integer. The DateTime requirement is a leftover from the OLAP cube partition days. While it is theoretically possible to specify partition boundary rules at a level lower than date (or on a totally different column) the default is that the smallest partition you can get is a daily partition (Always in UTC, mind you).
Just checking in on your earlier question regarding the use of Datetime columns for Incremental Refresh in Power BI.
lbendlin Irwan provided guidance explaining why Datetime is currently required (even for date-only data) and how appending a midnight timestamp is a valid workaround to balance cardinality and refresh behavior.
Could you please confirm if the provided solution helped in your scenario?
Let us know if you're still facing any blockers we're happy to assist further if needed!just circling back on your earlier question about using Datetime columns for Incremental Refresh in Power BI.
lbendlin Irwan had explained why a full Datetime column is required even when working with date-only data and suggested appending a midnight timestamp as a practical workaround to maintain refresh compatibility while keeping cardinality low.
Could you let us know if this approach worked for you?
If you're still running into any challenges, we're here to help!
hello Priti_Gaikwad
as far as my knowledge, incremental refresh does refresh partial of your data depend on range of date.
with incremental refresh, you need to set from when to when the refresh will be done.
Other than that, the data will not be refreshed.
the performance increase from incremental refresh comes from reducing the data needed to be refreshed.
Thank you.
Thanks for your responses
My question is at one side Power bi suggest separate date and time in individual column as best practice while other hand for increamental refesh it forces you to use a column with DateTime data type even if your dataset only contains dates and no time component.
The best practice recommendation is valid for incremental refresh setups as well, guaranteeing that all time values inside a date will sit in the same partition. You can do the conversion at the last minute, via
let d = Datetime.From([Date value]) in d >= RangeStart and d < RangeEnd
Hi Priti_Gaikwad
Just checking in on your earlier response posted by lbendlin regarding the incremental refresh setup. Were you able to try out the approach earlier shared with the M code and is the issue resolved ?
