Power BI refresh dataflow keep a snapshot of data

Question

my requirement is to be able to save the past state of an entity at a certain point of time. Currently when the dataflow is refreshed, the past state is lost and only the current one is displayed. Is there a way to do it? I tried setting up the incremental refresh but not sure if it fits the requirement.

The ideal would be having the table with a column snapshot date that is populated by the current refresh time and at each refresh this table will be populated by new rows and a new refresh time instead of replacing all the rows.

Answer 1

I don't believe what you want to achieve is possible solely in PowerBI currently, since it's merely a consumer of the data for reporting purposes. The data itself lives in an external system, like a SQL Server database for example, and is at most cached in a PowerBI report at a single point in time, as opposed to data versioning.

Instead you'd likely need to implement a data change tracking type of feature in your data source and have PowerBI query off of that. If you are using SQL Server, then the following are some ways you can track data changes to achieve your goal:

1. Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.

2. Temporal Tables - System versioned copies of the user defined tables that track changes.

3. Change Tracking - Automated tracking of changes for your user defined tables.

4. Change Data Capture - Keeps track of DML changes to the specified user defined tables.

Note the above is sourced from my DBA.StackExchange answer to this question.