Forum Discussion
incremental refresh policy
Hi Community..!!
I am using incremental refresh in the power bi , 1 year data and incremental refresh on 2 days on updated date, we have kept refresh twice a day in power bi workspace, but report is not showing correct order status, if we keep incremental refresh on 3 months on updated date then order status are getting correct but not on 2 days refresh.
what will be the issue? does keeping two days on updated date will not work?
how the incremental refresh works on updated date?
why the data is wrong?
Could anyone please help me on this?
I am attaching screenshot for your reference.
Thanks in advance..!!
15 Replies
Hi prasadpatil020,
Thanks for reaching fabric community will happy to assist.This usually comes down to how incremental refresh behaves with the Updated Date column.
With a 2-day window, Power BI only refreshes records where the updated date falls in the last 2 days. So:
- If an order was updated yesterday → it gets picked up
- If it was updated 3 days ago → it gets missed (As same as you mentioned)
That’s why things look correct when you use a larger window (like 3 months), but not with 2 days.
- You can try increasing the refresh window to around 7-14 days may give you accurate result
For more you can go throgh the documentations below.
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configureI might be off, but… IR won’t reopen a partition if the mod‑col didn’t tick, so ur 2d window just closes it before late status changes land. just widen that window a bit, and if it still sticks, run one full refresh once to thaw the sealed parts.
If order status changes for records where updated date is not refreshed correctly or falls outside that window like late updates, backdated changes or incorrect timestamps then those changes will not be picked up leading to incorrect status in the report.
When you extend it to 3 months then more partitions are refreshed, so those missed updates get corrected which is why it appears to work.
Likely causes are updated date column is not changing on every status update, source system updates old records without updating the timestamp, data delayed updates beyond 2 days
Ensure updated date is a true last modified column or increase the refresh window like 7 to 30 days to capture late changes
how the incremental refresh works on updated date?It doesn't natively. That's why it is called Incremental Refresh and not Differential Refresh.
You can choose to enable change tracking ("Detect data changes") but that comes with a large storage penalty.
Please read this documentation, it has good guidance for your scenario Advanced Incremental Refresh and Real-Time Data With the XMLA Endpoint in Power BI - Power BI | Microsoft Learn
Hi prasadpatil020,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to Kagiyama_yutaka, lbendlin, krishnakanth240, Lodha_Jaydeep, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member Kagiyama_yutaka, lbendlin, krishnakanth240, Lodha_Jaydeep, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Hi prasadpatil020 ,
Could you please confirm whether your issue has been resolved? If not, let us know if you need any additional details or clarification from our side.
Hi V-yubandi-msft ,
I have implemented changes to the incremental refresh and am monitoring the results for a few days. Once it meets my expectations, I will accept one of the solutions shared here.
Thanks,
Prasad PatilYeah, sure thanks for the information.
Hi Community..!!
My data is sourced from GCP, where the underlying table is partitioned on the created_at column. In Power BI, I have configured Detect Data Changes using the updated_at column.Our requirement is to retain all historical data while also ensuring that records updated after their initial creation are refreshed and reflected in the report. However, since the source table is partitioned by created_at, updates to records belonging to older partitions may not be picked up during incremental refresh, even when their updated_at value changes.
In summary, we need a solution that allows us to:
- Retain all historical data.
- Refresh only records that have been updated recently.
- Avoid reloading the entire dataset during each refresh.
The challenge appears to be that the source data is partitioned on created_at, while the refresh requirement is driven by updated_at.
"The challenge appears to be that the source data is partitioned on created_at, while the refresh requirement is driven by updated_at."
Partition boundaries must be set by immutable fields, so created_at is exactly the right field for that, and "updated_at" is exactly the wrong field.
Remember that "Detect Data changes" will reprocess the ENTIRE partition, not just the changed records.
If you change your filter criteria to the updated_at column instead of the created_at column all the previous solutions should still be valid as the updated records requirement will still be in place but you will be refreshing records that have been updated recently and archiving (not refreshing) records that haven't been changed recently.
You will most likely need to extend your archive data setting from 6 months to something like 5 years or whatever your requirement is for the dataset. This will create partitions starting at that time forward.
I think the real question to answer is how many months back can a record be changed after it's created date? That will help determine your incremental refresh time as you will need to include that many months potentially.
lbendlin has great insight in his response that confirms you will need to stick with the created_at for partitioning.
The trick will be finding the right balance of how far back to things get updated and what time frame needs to be incramentally refreshed.
We are currently displaying 6 months of data. However, any order from the past 5 months can still be updated, and we are not certain how far back an order may be updated.
To address this, we are considering removing the partitioning from the GCP backend table itself. This would allow us to retrieve all updated records from the source system without being restricted by the current partitioning strategy. On the Power BI side, we can then implement an incremental refresh policy using the Updated Date column, so that only records with recent updates are refreshed while still ensuring that all historical updates are captured.
