Implementing DST time

[vpatel55]

I have a dataset with times in UTC. I would like these converted into GMT / DST (depending on the time of year).

When importing the data, changing the type to "Date/Time/Timezone" adds the correct offset. For example, "26/09/2019 11:05:01 +01:00" in the below illustration. However, it appears that this offset is not actually usable. If I apply the query, the offset does not appear in Power BI, and it continues to read 11:05:01 instead of 12:05:01.

How do I therefore make the offset available to actually use? I have tried the "Using locale..." but this does not appear to do anything in this dataset. In a different dataset, it does actually implement the change, but only to one column. If I try the same in a subseqent column, then the first column becomes undone.

I have seen two good articles (links at the bottom) regarding implementing this in M here, however the articles are quite old and I'm still trying to learn M. So I'm hoping that this can now be done natively. Indeed, it does appear that it is detecting the required offset, and it just needs to be applied?

Any help would be appreciated. Here is the PBIX file: https://www.dropbox.com/s/qemefkbi6lyz95n/DataTime%20sample.pbix?dl=0

https://blog.crossjoin.co.uk/2017/03/28/daylight-saving-time-and-time-zones-in-m/

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

[amitchandak]

@vpatel55 , refer if this can help

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

[vpatel55]

Thanks for the response @amitchandak . I tried the "Local time" option. This added the "Calculated Local Time" step. However the actual time did not change at all. I have tried uploading it to the Power BI Service, and the times also do not appear adjusted for DST.

I have not tried the "DateTimeZone.SwitchZone " function, as it appears that this will hard code an offset, and not take into account GMT and DST? Thanks.

[dax · Accepted solution]

Hi @vpatel55 , 

You could refer to your first link to get the result. It seems that convert your sample datetime as 0 timezone, then convert it to datetime based on your location.

You could refer to below sample for details.

Best Regards,
Zoe Zhi

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

[vpatel55]

Hi @dax , that works, thanks so much for your help and also the sample file.