How to Convert Timestamp to Dates in Power BI?

by

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:

  1. 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")
)
  1. Add a card visual to your report
  2. Drop the “Last Refresh” field onto the card

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:

  1. 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"
)
  1. 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.