Incremental Refresh, Detect changes not working

[MadBern85]

Hi

I can't seem to figure this out.

I'm testing Incremental Refresh and can only get it to "half work".

I have a test dataset, going back 6 months (April 15th, 2024), with columns: ID, CallDate, AgentID, AnsweredCalls, MissedCalls, ForwardedCalls, AdjustedDate.

As a rule AdjustedDate is initially the same as CallDate.

I have set up a dataflow in a premium workspace which gets the dataset from SQL Server and if I configure Incremental Refresh as shown in the picture below, everything works fine. 

If I for instance increase the different call values by 5 on the latest record/latest CallDate and refresh the dataflow - I can instantly (after refresh) see the change in the Desktop Report I have connected to the Dataflow. But if I apply the same change to the first/oldest record, no change is picked up. Which is correct, since the change is made in the archived data and not in the last 14 days.

But if I however configure the Incremental Refresh like this:

Not only does changes to a record on April 15th not get picked up (AdjustedDate is then today's date), but changes to the latest record, which is still in the 14 day window, doesn't get picked up either.

I should add, which only confuses me further, that if I run a refresh on the Dataflow on PBI service and review the table directly in PBI Service by going here:

I can see the updated values with the updated datetime in AdjustedDate, but if I refresh the report in Desktop, no changes comes.

What am I missing here?

Thanks in advance for any insight/input.

Regards

Mads

[lbendlin]

Think of the participating partitions. You specified 14 days/ 24 months.  That means the "hot" refreshes will flush and fill the most recent 14 day partitions, but ONLY if the AdjustedDate in these partitions is different. If the AdjustedDate for partition "today minus 12" didn't change then the partition will not be refreshed even though it is a member of the "last 14 days"  group.

[MadBern85]

Hi Ibendlin, and thanks for the response.

But the AdjustedDate has changed. Everytime I make a change to the values I run GETDATE() to get the current datetime on the value in AdjustedDate.

[lbendlin]

Have you given the Power BI Service a chance to actually set up the partitions and fill them?  Every time you make structural changes to your report and/or semantic model the incremental partitions will be reset.

[MadBern85]

I believe so. I even rebuilt the dataflow from the ground up with the final settings for Incremental Refresh.

I have gotten one step further though - if I update values where CallDate is within the 14 days, these updates get picked up in the refresh. But I still can't get updated values for records where CallDate is before 14 days and AdjustedDate is within 14 days.

[lbendlin · Accepted solution]

That is by design.  "warm" partitions are not part of the change tracking.

Extend your "hot"  window.  For example 60 days instead of 14.

[MadBern85]

I started realizing that that would be the solution.

Thanks for the help.