Multiple DateTime Columns [In UTC] - How to Show in Different TimeZone [IST]

[RKM]

Experts,

In my PBI I have a big bulky table (Coming from SQL Server) with many columns among which 3 Datetime Columns are there.

StartTime_UTC, EndTime_UTC, Deadline_UTC

Now user wants to have an option to See these Fields in IST.

[Actually some users infact asking if we can give option to let user select which timezone to view]

Now one option for me is of course either I add 5hr 30mins to each columns and bring 3 additional columns either from SQL Query or Derived Column in Power BI.

But considering my table is big and lots of column already I need for my PBI dashboard, I am bit hessitent about these additional IST columns. Whether I should really go ahead and add them or there is any smarter way (performance factor etc.) to convert and/or allow user to choose timezone? 

Thanks much

[v-yangliu-msft · Accepted solution]

Hi  @RKM ,

I created some data:

Here are the steps you can follow：

1. Use Enter data to create a Slicer Table.

2. Create measure.

Measure =
MAX('Table'[Date])+DIVIDE(value(right(SELECTEDVALUE(Slice[Timezone]),1)),24)

3. Result:

Select the time zone in the slicer and the date will change accordingly

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[RKM]

This sounds very very promising. I'll give it a try shortly.

Still a question - From performance stand point, is it better doing in PBI or bringing it in from SQL (i.e. both Times in seperate columns. I am just worried I already pull lot of columns and if in SQL then I need to pull more columns per row). Probably your approach would be far better.

[amitchandak]

@RKM , refer if thes can help

https://www.thepoweruser.com/2019/10/21/handling-different-time-zones-in-power-bi-power-query/

https://whitepages.unlimitedviz.com/2020/10/dynamic-time-zone-conversion-using-power-bi/