Snapshot of data

[Yousra]

Hello, 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.

[selimovd]

Hey @Yousra ,

you could set it up in the dataflow with incremental refresh, but you can never really change anything or all the historic data will be lost. So I'm not a big fan of handling snapshots with incremental refresh, because from my point of view it's not a stable solution

What exactly is your data source? If it's a SQL database just create a job that is loading your data once per day into another table with an additional "LoadDate" column. Like this you have the history on a daily basis available. Also when you want to change something in the future that is then possible.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Yousra]

Thank you for your answer. 

Can you please tell me how can I do it with incremental refresh? I've tried adding a loadDate column but at each refresh it gets replaced.

The datasource is Salesforce so there is no SQL Database

[v-deddai1-msft]

Hi @Yousra ,

The incremental refresh for dataflow only works for power bi premium. And you can follow the steps in https://docs.microsoft.com/en-us/power-query/dataflows/incremental-refresh 

And you can create two entities in one dataflow one for transcational data and the other fot historical data . For more details, please refer to https://visualbi.com/blogs/microsoft/powerbi/historical-data-preservation-using-power-bi-dataflow/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai