Creating a date column which inserts the date the data was first loaded

Hi,

As per your requirement, the main challenge is that Power BI does not natively store the “first load” value for a row. Any function like TODAY() or NOW() will recalculate on every refresh, so it cannot preserve the original load date.

1. Approach (Use Power Query + Persistent Logic)

To achieve this, you need to preserve the first load date externally or through merge logic.

Fix (Use Power Query with Merge to Preserve First Load)

Step 1: Maintain a historical table
This table should contain:

• Entity_ID
• FirstLoadDate

Step 2: Merge with current dataset in Power Query

let
    Source = CurrentData,
    Merge = Table.NestedJoin(Source, {"Entity_ID"}, HistoricalTable, {"Entity_ID"}, "Hist", JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Hist", {"FirstLoadDate"}),
    AddDate = Table.AddColumn(Expand, "FinalLoadDate", each
        if [FirstLoadDate] = null then DateTime.LocalNow() else [FirstLoadDate]
    )
in
    AddDate

Logic Explained

• If Entity_ID already exists → keep existing FirstLoadDate
• If new record → assign current timestamp
• This ensures the first load date never changes

If you don’t have a historical table

You can derive first appearance in dataset:

First Load Date =
CALCULATE(
MIN('YourTable'[Date]),
ALLEXCEPT('YourTable', 'YourTable'[Entity_ID])
)

Note:

•   This gives first occurrence in data, not actual load timestamp
•   Works only if historical data is preserved

Hope this helps.

Thanks!

A DAX calculated column won't work here, it recalculates on every refresh, so it will always show the current date, not the first load date. You need persistence outside of Power BI.

The cleanest fix is to add the column at the data source. If you have access to your SQL table, add a first_loaded_date column with a DEFAULT GETDATE() constraint and no update trigger. It stamps the date once on insert and never changes.

ALTER TABLE YourTable
ADD first_loaded_date DATE DEFAULT GETDATE();