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.
- In the Data tab of Power BI, create a new column in your data source (not a new measure)
- 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”)
- 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.
- Update your report to use this new column in place of your original date/time column.
3 Replies to “Convert Time Zones in Power BI using DAX”
When there’s an empty value it defaults to 12/30/1899. How do I change that to text or leave blank?
Hey Nate! I’m creating a Call of Duty: Modern Warfare dashboard to show game stats pulled from an API. This was really helpful as I wanted to show the latest refresh datetime in Central Standard format…. because I’m also from Wichita! Good stuff. #ICT