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

[JJ3303]

Hi All,

I want to create a column in my table where it inserts the date the data was first loaded in PowerBI- this table will be continually updating. Does anyone have any ideas if there is a function I could use?

Thank you

[SamInogic · Accepted solution]

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!

[v-dineshya]

Hi @JJ3303 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @amitchandak , @SamInogic , @cengizhanarslan  and @ryan_mayu ,Thank you for your prompt responses.

Hi @JJ3303  ,  could you please try the proposed solutions shared by  @amitchandak , @SamInogic , @cengizhanarslan  and @ryan_mayu  ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @JJ3303 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[ryan_mayu]

@JJ3303 

I don't think we can do that. We can create a refresh date

= Table.AddColumn(#"Changed Type", "LoadDate", each DateTime.LocalNow(), type datetime)

However, it only contains refresh time. That means when you click refresh , the time will be updated.

[cengizhanarslan · Accepted solution]

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();

[SamInogic · Accepted solution]

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!

[amitchandak]

@JJ3303 , if you want when the data was refreshed, you can add new column in Power Query 

 = 

DateTime.FixedLocalNow()