How to Convert Timestamp to Dates in Power BI?

Are you struggling with timestamp conversions in Power BI, or need to show when your report was last refreshed? In this guide, I’ll show you how to handle timestamp conversions effectively and add a dynamic refresh time indicator to your Power BI reports.

Converting Timestamp to Date in Power BI

Let’s start with converting timestamps to proper dates. This is a common challenge when working with data from systems that store timestamps in different formats. You can handle this using either Power Query or DAX, depending on your needs.

Converting Unix timestamps with Power Query (seconds since epoch)

Open your Power BI report and select “Transform Data” to open Power Query Editor where you’ll perform the initial conversion of your timestamp data.
In Power Query, select the column containing your Unix timestamps, right-click and choose “Add Custom Column” to create a new column with the converted datetime values.
Enter a name for your new column such as “ConvertedDateTime” and use the following formula: #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YourTimestampColumn]) replacing YourTimestampColumn with your actual column name.
This formula adds the number of seconds in your timestamp to the Unix epoch start date (January 1, 1970), creating a proper datetime value that Power BI can understand and format.
If your timestamps are in milliseconds instead of seconds, modify the formula to: #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YourTimestampColumn]/1000) to divide by 1000 before conversion.
Click “Close & Apply” to save your transformation and return to the report view where you can now use the converted datetime column in your visualizations.

Using DAX for timestamp conversion

If you prefer to handle the conversion in your data model rather than Power Query, create a new measure or calculated column using DAX.
For a calculated column, go to the Data view, select your table, and click “New Column” from the ribbon menu.
Enter a DAX formula like: ConvertedDate = DATE(1970,1,1) + [YourTimestampColumn] / 86400 for seconds-based timestamps, where 86400 represents seconds in a day.
For millisecond timestamps, adjust the formula to: ConvertedDate = DATE(1970,1,1) + [YourTimestampColumn] / (86400*1000) to account for the millisecond precision.
Format your new column as a date or datetime depending on whether you need to display time components in your reports.

Once in the modeling phase of your project you can also handle the conversion in DAX, by using the following formula:

ConvertedDateTime = 
VAR UnixEpoch = DATE(1970,1,1)
RETURN
    UnixEpoch + [TimestampColumn]/(24*60*60)

Handling time zone conversions

If your timestamps are in UTC but you need to display local time, add the appropriate offset in your conversion formula.
In Power Query, modify your formula to include the time zone offset: #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YourTimestampColumn]) + #duration(0, YourTimezoneOffsetHours, 0, 0).
For Eastern Time (UTC-5), you would use: #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YourTimestampColumn]) + #duration(0, -5, 0, 0).
Remember that time zone offsets may change due to daylight saving time, so consider whether you need to account for these seasonal changes in your reports.

Adding Last Refresh Time to Your Report

Now, let’s add a refresh time indicator to your dashboard. This is crucial for users to know how current the data is. You can show refresh time in Power BI reports using these steps:

Create a new table using DAX:

RefreshTime = 
SELECTCOLUMNS(
    CALENDAR(2020,1,1,2030,12,31),
    "Current DateTime", NOW(),
    "Last Refresh", FORMAT(NOW(), "dd-mmm-yyyy hh:mm AM/PM")
)

Add a card visual to your report
Drop the “Last Refresh” field onto the card

Validate timestamp unit and time zone before conversion

Start by preserving the working state and defining one repeatable test. Retain the original timestamp beside the converted value. Record the account, device or client, and exact symptom so every later test uses the same starting point. Avoid combining resets, reinstalls, and permission changes because a successful result would not identify which action mattered.

Establish a reliable baseline

Test daylight-saving boundaries and invalid or blank records. Use non-sensitive sample data and a standard user account whenever possible. If the sample succeeds, compare its location, ownership, policy, and data shape with the failing item rather than widening the repair to the whole application.

Choose refresh-time or report-time conversion deliberately. Preserve the original file, configuration, or identifier until the result is confirmed. A locked option or organization message is evidence of an administrative boundary, not an invitation to bypass policy.

Confirm the result from the user’s view

undefined. Close and reopen the relevant app, then repeat the ordinary workflow. Check a second client or observer only when it answers a specific question about synchronization, permissions, hardware, or service availability.

When results differ, note the app version, account type, time, network, and exact error. That information separates a device problem from account, data, permission, or service behavior and gives support a focused case. Remove personal names, addresses, document text, and tokens from screenshots before sharing them.

Have another authorized user repeat the shortest safe test if shared access is involved. Their result may expose owner-only privileges, stale state, or an assumption about storage. Keep the known-good configuration available until the change survives a restart, refresh, or normal work cycle, and reverse experimental settings that did not help. Check the outcome again after the next scheduled synchronization when timing can affect the feature. Use a disposable test item when the action could change shared data or notify other people.

Troubleshooting Common Issues

Here are solutions to common timestamp conversion challenges:

Invalid timestamp values: Filter out nulls and zeros before conversion
Time zone mismatches: Use DATEADD() to adjust for local time
Performance issues: Create calculated columns instead of measures for static timestamps
Refresh time not updating: Ensure your dataset refresh settings are properly configured

Advanced Tips

For more complex scenarios:

Create a dynamic refresh indicator:

LastRefreshStatus = 
VAR CurrentTime = NOW()
VAR TimeSinceRefresh = DATEDIFF(RefreshTime[Current DateTime], CurrentTime, MINUTE)
RETURN
SWITCH(
    TRUE(),
    TimeSinceRefresh <= 60, "Updated Recently",
    TimeSinceRefresh <= 180, "Updated Today",
    "Needs Refresh"
)

Add conditional formatting to your refresh time visual based on the age of the data. You can learn more about formatting in Power BI tables for enhanced visibility.

Conclusion

Proper timestamp handling and refresh time display are essential for maintaining accurate, transparent Power BI reports. By implementing these solutions, you’ll ensure your users always know when the data was last updated and can trust the timestamp conversions in your visualizations.