Why does Incremental Refresh require DateTime when data contains only date?

[Priti_Gaikwad]

 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?

[Priti_Gaikwad]

@v-aatheeque  @lbendlin @Irwan 

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.

[lbendlin · Accepted solution]

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

[Priti_Gaikwad]

@lbendlin  Will check this solution & update u

[v-aatheeque]

Hi @Priti_Gaikwad 

Just following up to see if you had a chance to try out the suggested solution by @lbendlin  with the Datetime. From conversion. Did it help resolve the issue on your side ?

[Priti_Gaikwad]

@lbendlin @v-aatheeque 

What I did is with DateTime.From coversion I created custom column with my original Date column and then I set RangeStart & RangeEnd date filter on that custom column  and Finally I hide that column in model and applied Incremental refresh 

Please tell me whether this approach is correct?

[lbendlin]

You can do that as well but it will require (some) additional storage for that column.  If it works for your scenario then go for it.

[Priti_Gaikwad]

@lbendlin Please tell me if have any altrenate to this which could save the storage.

[v-aatheeque]

Hi @Priti_Gaikwad 

We haven’t heard from you on the last response posted by @lbendlin  and was just checking back to see if your query got answered. Otherwise, will respond back with the more details and we will try to help.

[Priti_Gaikwad]

@lbendlin @v-aatheeque 
Thanks for following up. Above solution worked for me

[lbendlin]

As I mentioned in my earlier reply you can do the conversion/casting as part of the filter step in Power Query,

[v-aatheeque]

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 ?

[lbendlin]

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).

[v-aatheeque]

Hi @Priti_Gaikwad 

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!

[v-aatheeque]

Hi @Priti_Gaikwad 

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!

[Irwan]

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.