How to keep historical data after every refresh?

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.

pickingapples

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