Capturing daily snapshots in Power BI when your source only shows today

Some systems only ever show you "right now." An inventory system tells you what is on hand today, a ticketing tool shows how many tickets are open this minute, and a CRM shows the current pipeline value. Ask any of them what the number was three weeks ago and you get a blank stare, because the system overwrote yesterday's value with today's. This guide is for anyone who needs to report a trend (units on hand over time, open tickets week over week, pipeline at the end of each month) from a source that keeps no history. By the end, you'll have a daily snapshot table that quietly grows one dated copy at a time, and a report that can show the line going up and down.

Before you start

You'll need a few things in place first:

• A Power BI (or Microsoft Fabric) workspace where you can create a dataflow. A Pro license covers basic dataflows; Dataflow Gen2 and a Fabric capacity give you more room and the built-in incremental refresh option.
• A connection to your source (a SQL database, an API, a SharePoint list, an Excel file on OneDrive, or whatever holds the "today" numbers).
• Edit rights in the workspace, and the source credentials Power BI will use to refresh.
• A rough idea of how often you need a snapshot. Daily is the common case, and this guide assumes daily.

The core idea is simple: every day, we take a copy of the current numbers, stamp it with today's date, and add it to a growing table. We never overwrite. Over a year you end up with 365 dated copies, and that is your history.

Step 1: Decide what one snapshot looks like

Before touching Power BI, write down the columns for a single day's snapshot. Keep it small and specific. For an inventory example you might capture:

• Snapshot Date (the date you took the copy)
• Product ID
• Product Name
• Quantity On Hand
• Location

The key column is Snapshot Date. Everything else is just the current state of the source. One row per product per day means that if you carry 400 products, each daily snapshot adds 400 rows.

Step 2: Create the dataflow that reads "today"

In the Power BI service (app.powerbi.com), open your workspace and select New, then Dataflow (or Dataflow Gen2 if you're on Fabric). In the dataflow editor:

1. Select Get data and pick your source connector (for example SQL Server, Web API, or SharePoint).
2. Sign in or enter the connection details, then choose the table or query that holds the current numbers.
3. In the Power Query editor, trim to the columns from Step 1. Right-click columns you don't need and choose Remove.
4. Rename the query to something clear, like "Inventory Today."

At this point you have a query that returns the current state. It has no date column yet.

Step 3: Add the snapshot date

Still in Power Query, add a column that stamps each row with today's date.

1. Select Add column, then Custom column.
2. Name it Snapshot Date.
3. For the formula, enter DateTime.Date(DateTime.LocalNow()). That returns today's date with no time component.
4. Select OK, then set the new column's data type to Date.

Now every row that flows through carries the date the refresh ran. When this runs tomorrow, the same rows come through stamped with tomorrow's date.

Step 4: Set the destination to append, not replace

This is the step that creates history instead of overwriting it. You have two common ways to do it.

Option A: Dataflow Gen2 with incremental refresh (the tidy way)

If you're on Fabric with Dataflow Gen2, you can write to a data destination (a Fabric Warehouse, a Lakehouse table, or an Azure SQL Database) and turn on incremental refresh.

1. In the Gen2 dataflow, select the query, then choose a Data destination and point it at your warehouse or database table.
2. Right-click the query and select Incremental refresh.
3. Choose Snapshot Date as the column to filter on, and set a bucket (for example, by day).
4. Save and configure a daily scheduled refresh.

With incremental refresh, the dataflow only touches the current day's bucket on each run and leaves every earlier day untouched. Data already written for past dates stays put, which is exactly the append behavior we want.

Option B: A scheduled export to storage (the no-Fabric way)

If you don't have Fabric, you can still build history. Have the dataflow (or a scheduled Power Automate flow, or a small scheduled script) write each day's snapshot as a separate dated file into storage you control, for example a folder in SharePoint, OneDrive, or Azure Blob Storage. Name files by date, like inventory-2026-06-24.csv. Then a second query in your model reads the whole folder and stacks the files into one table.

Step 5: Schedule the daily refresh

A snapshot pattern only works if it runs every day, automatically.

1. Back in the workspace, find your dataflow and open its settings (the gear or the three-dots menu, then Settings).
2. Under Scheduled refresh, turn it On.
3. Set a time of day that comes after your source is fully updated for the day. For inventory, that might be early morning after overnight receiving is posted.
4. Confirm the refresh credentials are valid so it won't silently fail.

Pick one consistent time. If a refresh is skipped, you simply have a gap for that date, which is usually fine, but a consistent run time keeps the history clean.

Step 6: Model the snapshot table for reporting

In your Power BI semantic model (dataset), bring in the snapshot table and connect it to a date table.

1. Create or reuse a Date table and mark it as your date table.
2. Build a relationship from Date[Date] to your snapshot table's Snapshot Date.
3. Write measures that respect the snapshot grain. A simple Total On Hand = SUM('Inventory Snapshot'[Quantity On Hand]) works for a single selected day. For an end-of-period view, use a measure that picks the latest snapshot in the period, for example with LASTDATE over Snapshot Date.

The trap to avoid: if a user selects a whole month and you just sum quantity on hand, you'll add up 30 daily copies and get a number 30 times too big. Decide whether each visual shows a point in time (one day) or a trend (a line with one point per day), and write the measure to match.

Step 7: Verify it's actually building history

Don't wait a month to find out it isn't working.

1. Run the dataflow refresh manually today and confirm rows land with today's Snapshot Date.
2. Tomorrow, check that yesterday's rows are still there and a new set with tomorrow's date has been added. You should see the row count grow by one day's worth.
3. Build a quick line chart of Total On Hand by Snapshot Date. After a few days you'll see a short line forming. That line is the history you couldn't get before.

If the row count isn't growing, your destination is likely replacing instead of appending. Re-check Step 4 (incremental refresh on, or files writing to distinct dated names).

What to do next

Once a few weeks of snapshots have accumulated, you can report week-over-week and month-over-month trends the source never kept. From here, common next steps are adding a second snapshot table for another "today only" system, setting a retention rule so the table doesn't grow forever, and building an end-of-month view for finance. If you'd like a hand designing the snapshot grain or wiring up incremental refresh in Fabric, that's the kind of thing we set up for clients regularly, so reach out and we'll scope it with you.