Resolving Time Zone Issue in PowerPivot
Key Technologies: Power BI, PowerPivot, Business Intelligence
Note: This article assumes experience on PowerPivot for Excel 2013/2010
Many a time data needs to be imported and processed from systems belonging to different locations and hence having different time zone. For example, consider the following scenario.
A user residing in India having IST time zone might need to import data into PowerPivot model from CRM Online System having CEST as time zone required for Power BI Dashboards or for performing Data Analysis. There might be critical business data related to revenue or sales that matters the most. If date time is not properly converted or processed, end user might see incorrect output. When working with PowerPivot, after importing data into model, new calculated columns needs to be created before working on revenue reports or any business related critical data so that proper reports/results are displayed to end user.
Here, we are converting to match data for CEST time zone. Similarly for other time zone, we can add/subtract UTC offset as shown below:
Central European Summer Time (CEST). Offset UTC +2:00 hours
Eastern Standard Time (EST). Offset UTC -5:00 hours
Central Standard Time (CST). Offset UTC -6:00 hours
Mountain Standard Time (MST). Offset UTC -7:00 hours
Pacific Daylight Time (PDT). Offset UTC -7:00 hours