Convert Time Zones in Power BI using DAX

timezonecorrection.png

Even if your SharePoint site’s regional settings are correct (or whichever data source you’re pulling from), Power BI could convert it to the wrong time zone upon import. It’s a quick fix, luckily. Instead of using your “modified,” “created” or other date field in your report, we’ll create a new calculated column in Power BI to use with an accurate time zone.

  1. In the Data tab of Power BI, create a new column in your data source (not a new measure)
    newcolumn
  2. Enter the following equation, replacing red text with your unique data:NewColumnName = FORMAT(DataTableName[ColumnName] – TIME(5,0,0), “General Date”)For example:
    LocalTime = FORMAT(Tweets[Created]-TIME(7,0,0),”General Date”)
  3. Then, under the modeling tab in the formatting section, you can format the calculated column to display data in any number of ways. This is how data will show up for tooltips and perhaps the report itself depending on how you’ve set it up.
    dateformats
  4. Update your report to use this new column in place of your original date/time column.

 

Advertisements