Which date field should I use for incremental refresh: Call Date vs. Update DateTime?(with 2-Hour Sc

[manoj_0911]

Which date field should I use for incremental refresh: Call Date vs. Update DateTime? (with 2-Hour Scheduled Refresh)

Hi Power BI Experts,

I'm working on a Power BI dataset that uses incremental refresh, and I'm unsure which date field to use for setting up the policy. My dataset includes:

• CALL_DATE: The original date the record was created (e.g., when a call occurred)

• UPDATE_DATETIME: The timestamp when the record was last updated

My database administrator mentioned that:

"Data is not always updated in order. Sometimes historical records (older CALL_DATEs) get updated later."

I'm planning to configure incremental refresh as follows:

• Store: last 24 months of data

• Refresh: last 2 days

• Refresh Frequency: Every 2 hours, scheduled in the Power BI Service

My question is:

➤ Should I base my incremental refresh on CALL_DATE or UPDATE_DATETIME?

I'm concerned that if I use CALL_DATE, I may miss late-arriving updates (e.g., a May 1st record updated on May 26th). I want to ensure that all recently updated records are included in the refresh window, even if their original call date is older.

How should I approach this to avoid missing updated records?

Thanks in advance!

[ajaybabuinturi · Accepted solution]

Hi @manoj_0911,
I'm not sure how the data you have but you can set up a Incremental refresh on CALL_DATE, make sure it shoule be date/time data type. If the CALL_DATE column doesn't have time stamp then add a duplicate column of CALL_DATE and trnsform it date/time data type.

And set up the incremental policy as follows.

• Archive:  2 Years of data as it creates only two partions in backend (1/1/2023 to 2/28/2024). Which ensures that to reduce time as compared to Months.

• Incremental Refresh data : 3 months as it brings up historical data which is updated in last 3 months. (3/1/2025 to 5/31/2025)
  (3 months should be decided based on histrical data updated frequency)

Example of the time frames:

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[v-venuppu]

Hi @manoj_0911 ,

I hope this information is helpful. Please let us know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @manoj_0911 ,

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. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @manoj_0911 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ajaybabuinturi for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu · Accepted solution]

Hi @manoj_0911 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ajaybabuinturi for the prompt response.

You should base your incremental refresh on UPDATE_DATETIME, not CALL_DATE.

The Reason Why:

CALL_DATE represents when the record was created.

UPDATE_DATETIME shows when the record was last changed.

If you use CALL_DATE, Power BI will only look at new records based on that field. So if a record from May 1st is updated on May 26th, and your refresh window is the last 2 days, Power BI will skip it — because the CALL_DATE is outside the refresh window.

Using UPDATE_DATETIME ensures that any record updated recently (even if old) gets included in the refresh.

set up the incremental policy as follows:

• Incremental refresh column: UPDATE_DATETIME
• Archive: Last 24 months
• Refresh: Last 2–3 days (since you're refreshing every 2 hours, 2 days is usually enough)
• Make sure UPDATE_DATETIME is a datetime type in Power BI.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

 

[ajaybabuinturi · Accepted solution]

Hi @manoj_0911,
I'm not sure how the data you have but you can set up a Incremental refresh on CALL_DATE, make sure it shoule be date/time data type. If the CALL_DATE column doesn't have time stamp then add a duplicate column of CALL_DATE and trnsform it date/time data type.

And set up the incremental policy as follows.

• Archive:  2 Years of data as it creates only two partions in backend (1/1/2023 to 2/28/2024). Which ensures that to reduce time as compared to Months.

• Incremental Refresh data : 3 months as it brings up historical data which is updated in last 3 months. (3/1/2025 to 5/31/2025)
  (3 months should be decided based on histrical data updated frequency)

Example of the time frames:

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.