Forum Discussion
How to keep historical data after every refresh?
Hi everyone,
I’m looking for guidance on how to maintain weekly historical data of my CRM table in Power BI.
Current setup:
- My CRM data is coming from multiple sources.
- Only 1 sources is from our internal SSMS, other sources are external.
- The dataset refreshes 4 times per day via scheduled refresh.
- The CRM table always shows the latest values after each refresh (which is expected).
Requirement:
I want to store a weekly historical data of CRM (once per week, e.g. every Friday at 11 AM) so that:
- Each week is preserved as a historical snapshot
- Users can compare week-by-week CRM values based on Forecast Category
- Previous weeks always show the same values as when they were originally reported
Problem:
- The report only shows the latest refreshed data
- When I select a previous week in the filter, the values change because the underlying data has been updated
How to:
- Capture and store CRM weekly historical data
- Ensure weekly historical data weeks remain unchanged even after new refreshes
Hi -Power BI cannot store historical states by itself. You must persist snapshots in a storage layer such as: Eg: , SQL Server (SSMS) or Lakehouse / Warehouse in Microsoft Fabric or Dataflow Gen2 storage or even if oyu ae using MS Fabric it works.
1. Create an append-only snapshot table (e.g., FactCRM_Snapshot) to store CRM data weekly. using above any approach.
then ,2. schedule a weekly job (Friday 11 AM) in SQL or Microsoft Fabric to insert current CRM data with a SnapshotDate.
3. Use this snapshot table (not the live table) in Power BI and relate it to a Date table.
4. Build measures on snapshot data so past weeks remain unchanged and comparable over time.Hope this helps.
Instead of Power BI reading the live CRM table directly, a separate process writes a weekly snapshot into a persistent table that grows over time. Power BI then reads that historical table instead.
Option 1) Dataflow Gen2 in Fabric (if you are on Fabric)
Create a Dataflow Gen2 that reads the CRM sources and writes to a Lakehouse table with an appended SnapshotWeek column. Schedule the dataflow once per week on Friday. The Lakehouse table accumulates snapshots permanently and Power BI reads from it.
Option 2) SQL Server
Since one of your sources is already SSMS, create a snapshot table there:
CREATE TABLE CRM_Weekly_Snapshot ( SnapshotDate DATE, -- all CRM columns here )Schedule a SQL Agent job every Friday at 11 AM:
INSERT INTO CRM_Weekly_Snapshot SELECT CAST(GETDATE() AS DATE), * FROM CRM_Live_Table
7 Replies
Hi -Power BI cannot store historical states by itself. You must persist snapshots in a storage layer such as: Eg: , SQL Server (SSMS) or Lakehouse / Warehouse in Microsoft Fabric or Dataflow Gen2 storage or even if oyu ae using MS Fabric it works.
1. Create an append-only snapshot table (e.g., FactCRM_Snapshot) to store CRM data weekly. using above any approach.
then ,2. schedule a weekly job (Friday 11 AM) in SQL or Microsoft Fabric to insert current CRM data with a SnapshotDate.
3. Use this snapshot table (not the live table) in Power BI and relate it to a Date table.
4. Build measures on snapshot data so past weeks remain unchanged and comparable over time.Hope this helps.
Power BI is not a persistent store. It loads whatever available data is at refresh time so historical data must be stored somewhere else otherwise the model will be overwriten with the most recent one. You can use Power Automate to run a query against a dataset and save the result as a csv in a SharePoint folder and then use that folder as the data source of a historical snapshopt. This blog discusses how to run a query against a datast - https://www.matthewdevaney.com/power-automate-run-a-query-against-a-power-bi-dataset/
pickingapples Incremental refresh feature in Power BI would be an best fit for this scenario. But for this feature to work you will need a date column in your source. If the date column is not there you might have to add it and configure the incremental refresh. Please go through the below documentaion and video to get started.
Incremental Refresh in Power BI: https://www.youtube.com/watch?v=RLE0WlZPjqQ
Documentation: https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
Power Automate weekly snapshot flow:
Recurrence trigger - Friday 11 AM
Power BI - Export To File for CRM table (CSV)
SharePoint - Create file CRM_Snapshot_YYYYMMDD.csv
Power BI dataset - SharePoint Folder connector loads all CSVs
DAX - Week Snapshot = MAX(CRM[SnapshotDate]) slicer
Instead of Power BI reading the live CRM table directly, a separate process writes a weekly snapshot into a persistent table that grows over time. Power BI then reads that historical table instead.
Option 1) Dataflow Gen2 in Fabric (if you are on Fabric)
Create a Dataflow Gen2 that reads the CRM sources and writes to a Lakehouse table with an appended SnapshotWeek column. Schedule the dataflow once per week on Friday. The Lakehouse table accumulates snapshots permanently and Power BI reads from it.
Option 2) SQL Server
Since one of your sources is already SSMS, create a snapshot table there:
CREATE TABLE CRM_Weekly_Snapshot ( SnapshotDate DATE, -- all CRM columns here )Schedule a SQL Agent job every Friday at 11 AM:
INSERT INTO CRM_Weekly_Snapshot SELECT CAST(GETDATE() AS DATE), * FROM CRM_Live_TableHi pickingapples ,
Thanks for reaching out to Microsoft Fabric Community.Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thanks to community members for actively participating and providing valuable guidance which helps in resolving queries.
Hi pickingapples ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.
