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.

Method 1: Using Power Query

Here’s the step-by-step process in Power Query:

  1. Open Power Query Editor and then select your timestamp column
  2. Add this custom column using the following formula:
= #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YourTimestampColumn])

For Unix timestamps in milliseconds, you will need to modify the formula to:

= #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [YourTimestampColumn]/1000)

Method 2: Using DAX

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)

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.