Daily Incremental Refresh with Delta Capture and Reporting

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Crud we're not allowed to access any kind of file sharing sites from work. I could give you some sample data with the actual header names maybe?

And my measure in my table:

need more snapshot dates.

I refined the post with two morte snap shot dates of changed data. thank you so much for your help so far 🙂

So weird, I went away for the weekend and came back and now it's not working. It's no longer converting changes in Overall Inherent Risk or Overall Control Effectiveness and displaying their changes. It's just calling them new records. The only thing I've changed since Friday was that I deleted the physical column "snapshot_dt" from each file as I had that in there for testing purposes. But in the Transform Steps in PowerQuery I added in a column "snapshot_dt" which was derived from the file name, which was imported from PowerQuery. So, really no change at all. All the same columns are present when the calculations occur as far as I can tell. I'm not sure why it's no longer work.

Edit: Now I see a difference. In the Friday screenshot  the column "Overall Inherent Risk" doesn't even exist in this structure but for some reason it was computing the change in Overall Inherent Risk, or at least it seemed to be doing that. In the image from today 4/15 that column is present but no changes are computed except to call existing records that have changed " New Records".

Edit2: I see another difference. You're using a Matrix in your PBIT and I'm using a Table visual. Is there a way to use a Table instead of Matrix?

Question:

[1] Can I use a table instead of a matrix visual? It would be nice to be able to see all of the details side-by-side. Although, the grouping on date of the Matrix is appealing!

[2] If I want to add other columns to monitor for change (e.g., Some other value changes from Standard to Intense), does it just involve adding additional AND IF clauses to the 'var chg' expression?

[3] How can I get count values out of this returned table of changes? Is there a way to count the number of changes or the total number of new records per day from this method?

Screenshot from This Morning (4/15)

Screenshot from Friday (4/12)

Hey man, for some reason the code is producing some interesting issues. If a record changes more than once it stops recognizing the change. It's almost like it's remembering that the original value from the date the record was loaded is now the same, and not showing it at the 'Date Level'. But it is detecting it at the 'Record Level'. Any thoughts on how to tweak that?

you originally asked to compare between two selected snapshot dates. Now it looks more like a list of dates, Please clarify,

I apologize I wasn't more clear. I should have better described the business process. Each day a new file will be added to the SharePoint directory. So each day when the service refreshes, I need to compare the current day's file with the previous day's file to see if there were changes.

I.e., tomorrow at 12:01 AM ET on 17 Apr 2024 I will receive a file for today 16 Apr 2024. The file will be stamped 04-16-2024.CSV and will need to compare itself against 04-15-2024.CSV. Or any two date values that are passed to the filter. I.e., if I say show me 04-01-2024 and 04-02-2024, what changs, if any, occurred there?

So at the end of the year I"m going to have 366 files (Leap year this year).

If you select exactly two dates then my code will compare between them.  If you select more dates then the code will compare between the smallest and largest selected dates.

Yes, it does that very well! But I noticed an interesting bug that if a record changes and then in the future that record changes again, the code doesn't detect at the "Date Level" that a change was detected. But if I expand the Date to show all of the records, it shows that the record was indeed changed.

Welcome to the world of Nyquist and Shannon.  That's why I prefer event based reporting over snapshots.

Nyquist and Shannon - theory of signal detection. I didn't get that far in my psychophysics/acoustics classes 😉 Is there any work around given my constraints of snapshot reporting? I'm still investigating with the DBAs and system admin to see if there is a 'lastupdated' or 'lastmodified' field on my source tables I could use. I assume this is the event based reporting you mean?

Either way, I'd be stuck with daily snapshots but if I had confidence that I could capture only valid changed records each day, it would limit the size of my snapshots and it would make the manual review easier.

Not so much signal detection but more sampling rate discussion. According to Nyquist and Shannon you need to sample at a rate at least twice as high as your change rate.  So let's say you have weekly snapshots - that means you will be blind to any changes that happen to your data within the week.   If you need better quality you need to increase the sampling rate (for example to twice a day)

If you want to be cute you can post-process your snapshots to throw away all rows that have not changed.

This isn't a large scale application. We're talking about less-than 100k records in each table. Some of them only  change once a year, some once a month. Some may change daily but those are definitely  smaller data sets.

It turns out I was able to get Date Created and Last Updated fields exposed in the reporting layer. So I can include those fields now if needed. Do you think that might help improve the code? I could only bring records that changed between Day A and Date B?

Some of them only  change once a year, some once a month. Some may change daily but those are definitely  smaller data sets.

That data is unsuitable for incremental refresh. Incremental refresh only works with immutable data.

You will need to implement your own partition management, up to and including periodic full refreshes across all partitions.

Based on your original message, I assumed I didn't actually need to use incremental refresh in the Service. Rather, because the records are so small, that continuously appending new files to the folder should be okay because PBI will be able to just look at the MAX file date and the next "max" file date (today v. yesterday) to keep the refreshes tidy.

Frankly, if your data volume is low, don't bother with Incremental Refresh.  Do a brute force flush and fill.

Appreciate your patience and time on this 🙂 Yea, brute force refresh is good. Can you think of any way to deal with the problem of a record changing on a given day and then changing again in a few days? Am I simply stuck with this issue in the fantastic code you wrote for me because of the Nyquist/Shannon sampling issue you described?

I'm just wondering if the Last Updated field could be of any help? 

Your only other option would be switch to CDC and event based reporting. In its simplest form that is a reference table with all changes to all fields being recorded one by one.  It's a bit more computation, but requires dramatically less storage, and allows you to recreate the state of each transaction for any point in time you choose. You can then also do things like jitter analysis and sankey flow diagrams etc.