Data Snapshots

[Anonymous]

I am trying to figure out how to go about taking a snapshot of some data. We keep a daily count of our active companies, but things are constantly changing in our database so the numbers always change. For instance, today I can get the exact active client count for 2/8/2018 which is 4,309. If I open up my PBI report in a few weeks, or even days, and want to look back to see what the active client count was on 2/8/2018, it would no longer read 4,309, but some other random number that could either be higher or lower since the data is constantly changing in our SQL database. Is there a way that each day I can take a snapshot of the date and client count on that day and have it stored in some additional table to reference? We are attempting to record customer retention and I just want the numbers as accurate as possible. Thanks!

[v-fengyz-msft · Accepted solution]

@Anonymous

Power BI does not have incremental load at present. This means that if you try to timestamp something at the time of query load, you will get updated values for the timestamp when it is refreshed.

Three options for your reference:

1. Storing and using information from a dynamic data source using PBI desktop

2. Use R script: auto export of data

3. Create a Trigger on source table to insert rows to your DataChangeLog table as soon as the data updated from source table in your database. Colde looks like below:

create trigger triggername On sourcetable

for update

as

insert into destinationtable (column) select column from TriggerTest1

Go

You can vote the idea: Allow for point-in-time snapshots for reports/dataset

Regards,

Pirlo Zhang 

[Kloo]

@v-fengyz-msft

Can you please provide an example of option 3 with sample tables? 

Thanks! 

[Recsant]

@v-fengyz-msft
I would also love to know more about the create trigger. I'm assuming it's M written into the advanced editor. Is there any documentation you could direct us too.

[Recsant]

@v-fengyz-msft
I would also love to know more about the create trigger. I'm assuming it's M written into the advanced editor. Is there any documentation you could direct us too.

[Omar1]

I'd like to follow your example for option 3.

Can you please show step by step directions for how to add like you would do it using example tables?