Skip to content
All insights
GrowPower BI & FabricHow-To GuideJune 10, 2026

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

The Short Version
  • Some systems only show current state, so you must build your own history.
  • Stamp each daily copy with a snapshot date and append it to the table.
  • Use a Dataflow Gen2 destination set to Append, or a dated file export to storage.
  • Write measures so a month selection shows a trend or one end-of-month figure.
Bottom line: A daily dated snapshot turns a source that only knows today into a table you can chart over time.

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. None of them can tell you what the number was three weeks ago, because each one overwrote yesterday's value with today's. This guide is for anyone who needs to report a trend from a source that keeps no history: units on hand over time, open tickets week over week, or pipeline at the end of each month. By the end, you'll have a daily snapshot table that grows by one dated copy each day, and a report that shows the line going up and down.

Daily snapshot pattern SOURCE Shows only today DAILY Append a dated copy HISTORY Snapshot table grows REPORT Trends over time
Each day adds one dated copy you can trend.

Prerequisites

You'll need a few things in place first:

  • A workspace on a Fabric capacity, a Fabric trial capacity, or a Power BI Premium capacity, where you can create a Dataflow Gen2. Option A in Step 4 uses it.
  • If you don't have one of those, Option B in Step 4 covers you. A Power BI Pro workspace supports only the older Dataflow Gen1, and a Gen1 full refresh reloads all of its data each time, so it can't keep history by itself.
  • A connection to your source (a SQL database, a web service, a SharePoint list, an Excel file on OneDrive, or whatever holds the "today" numbers).
  • The Member role or higher in the workspace, which Fabric requires to refresh a dataflow, and the source credentials the refresh will use.
  • A rough idea of how often you need a snapshot. Daily is the common case, and this guide assumes daily.

Every day, we take a copy of the current numbers, stamp it with that day's date, and add it to a growing table. Earlier copies stay as they are. Over a year you end up with 365 dated copies, and that is your history.

Step 1: Snapshot Columns

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

Snapshot Grain

Snapshot only the grain you'll report on, meaning the level of detail each row holds. If you only ever chart quantity by product and location, you don't need supplier notes or unit cost. A narrow snapshot table refreshes faster and costs less to store.

Step 2: Dataflow Source Query

In your Fabric workspace, select New item, then Dataflow Gen2, and give it a name. In the dataflow editor:

  1. Select Get data and pick your source connector (for example SQL Server, Web, 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: Snapshot Date

Still in Power Query, add a column that stamps each row with the snapshot date.

  1. Select Add column, then Custom column.
  2. Name it Snapshot Date.
  3. For the formula, enter DateTime.Date(DateTimeZone.FixedUtcNow()). That returns the current date in Coordinated Universal Time (UTC), fixed once per refresh so every row gets the same date.
  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. The next day's refresh stamps the same rows with the next day's date.

Time Zone

In the Power BI service and Fabric, the "local" time functions return UTC, so DateTime.LocalNow() gives you UTC there too. A refresh that runs in the evening in the US can fall after midnight UTC and stamp the next day's date. If your snapshot should follow your own calendar, shift the time before taking the date. DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(), -6)) gives Central time during standard time. The offset changes with daylight saving time, so document which zone the snapshot represents.

Step 4: Daily Append

This step turns a daily refresh into history. You have two common ways to do it.

Option A: Dataflow Gen2 Append

With Dataflow Gen2, each query can write to a data destination such as a Fabric Lakehouse table, a Fabric Warehouse, or an Azure SQL Database. The destination's update method sets what happens on each refresh. Replace drops the table's data and writes the new output. Append adds the new output to the rows already there.

  1. Select the query, then select Add data destination and pick your lakehouse, warehouse, or database.
  2. Choose a new or existing table, then select Next.
  3. Turn off Use automatic settings. Automatic settings use Replace.
  4. Set the update method to Append, check the column mapping, and save the destination settings.
  5. Select Save and run. The run writes the first day's snapshot.

Each refresh now adds one dated copy to the table and leaves earlier rows in place. A second run on the same date appends that date a second time, so avoid extra manual runs once the schedule is live.

Dataflow Gen2 also offers incremental refresh, which suits a different case. Its only supported update method is Replace: each run deletes and reloads the date ranges that changed. It also expects the source itself to carry a date or datetime column to filter on. Your source has no such column, which is the reason you're building snapshots.

Option B: Dated File Export

If you don't have Fabric, you can still build history. Have 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 query in your semantic model reads the whole folder and combines the files into one table. Keep the file name column and turn it into the Snapshot Date, since the file name carries the date.

Step 5: Daily Refresh Schedule

A snapshot pattern only works if it runs on a schedule every day.

  1. In the workspace, select the ellipsis (...) next to your dataflow, then Schedule.
  2. Set a daily frequency and a time of day that comes after your source is fully updated. For inventory, that might be early morning after overnight receiving is posted.
  3. Apply the changes.
  4. Confirm the connection credentials are valid. Fabric pauses a schedule after several days of consecutive failures and emails the dataflow owner.

For Option B, the flow or script runs on its own schedule. Pick one consistent time either way. A skipped refresh leaves a gap for that date, which is usually fine, and a consistent run time keeps the history clean.

Step 6: Reporting Model

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

  1. Create or reuse a Date table and mark it as a 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 basic 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 the LASTDATE function over Snapshot Date.

One mistake to avoid: if a user selects a whole month and you sum quantity on hand, you 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: History Check

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

  1. After the first run, confirm rows land with that day's Snapshot Date.
  2. The next day, after the scheduled run, check that the first day's rows are still there and a new set with the new date has been added. The row count should 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.

If the row count stays flat, check the update method in Step 4. It should read Append. For Option B, check that each day's file gets its own dated name.

Follow-On Work

Once a few weeks of snapshots have accumulated, you can report week-over-week and month-over-month trends for a source that keeps only today. Common next steps are a second snapshot table for another system that shows only today, a retention rule that limits how many days the table keeps, and an end-of-month view for finance. If you'd like a hand designing the snapshot grain or setting up the Dataflow Gen2 destination in Fabric, we set this up for clients and can scope it with you.

See where you stand. Then move forward.

Book a free intro call. We'll talk through where you are today and map a plan for growth, protection, automation, and alignment.

30 minutesNo obligationGet an initial estimate within one week