Desktop and Online version not showing the same time

[PeterGarant]

Hi all

I made a report in the desktop version, where I have some timestamps. But when I publish it to the online version it converts the times back to UTC:

Above is the data that I have in my dataset. I have converted the CreatedUTC into GMT and then I have converted it to just show the time as shown in GMT. Then just to try it out I made a latest and earliest times in the desktop version of the GMT times (These are the correct times):Then I published the dataset and report online, but then the times look like this (Browser is in danish, hence the translation, I tried changing it to English, but got the same times): 

Is this a known problem? And is there a way to make it show the same? 

Best Regards

Peter

[GilbertQ]

Hi @PeterGarant

I am going to assume that your "CreatedUTC" column is using a Date that comes from a created column where it is using a DateTime function looking at your local DateTime?

All the Power BI Servers worldwide (as far as I know) have their Date and Time as UTC. So when you upload your files, it then uses the servers local DateTime which is in UTC.

So what you would need to do is to offset your DateTime based on your location in the world. For example I am in Brisbane and I have to offset my DateTime to +10

You can refer to my Blog post here I explain for the Latest Refresh Date Time, how to set the Offset time: https://www.fourmoo.com/2017/03/28/power-bi-create-last-dataset-refresh-date/

[PeterGarant]

Hi @GilbertQ

The CreatedUTC is straight from my SQL server. I only transformed it to have the timezone in also. Our server logs in UTC times, but we work in GMT, so I made a DAX to calculate the GMT time, and then took the hour from that.

I find it funny that the desktop version and the online version isn't using the same times. And I also find it kind of odd that the online version needs to recalculate the time, when I have already done that myself, it's a strange behavior that it recalculates a time that I have set as i wanted it to be shown.

The problem would I foresee is that someone downloads the pbix file, and then they get the wrong times when they open it locally, cause with your solution it will be wrong on a local version.

But thank you for the answer, I guess I need to go to the place with ideas and put this as a want (still new to Power BI).

[PeterGarant]

Hi @GilbertQ

I just tried offsetting it 2 hours more, but when i publish it, it still shows the UTC time.

The way I create the GMT is as follows:

I transform the CreatedUTC to the data type: Date/Time/Timezone.

I then add a coustom column with the following DAX:

= DateTimeZone.RemoveZone([CreatedUTC]) + #duration(0, DateTimeZone.ZoneHours([CreatedUTC]), 0, 0)

This gives me the time in GMT, and shows up fine in the desktop version.

But when I publish it, then it shows the time in UTC. So there must be something that refers back to the CreatedUTC, cause even if I add 4 hours to make an offset, then I still get the UTC time when I publish it.

So is there something that I am doing wrong, or is this just "normal" behavior?

Best regards

Peter

[GilbertQ]

Hi there, as with my example I created this in the Query Editor. This is so that once the data is refreshed it stays as it should, and to ensure that it has the correct DateTimezone.

[PeterGarant]

Hi again

So I tried your solution, but i still get the same problem with the time being shown in UTC online.

You make it into text with your measure, so that is why it works in your case, but I actually need the times as time to work with them. So that solution won't work for me unfortunately 😞

So if anybody else has an idea of what can be done, then I'm all ears.

[PeterGarant · Accepted solution]

Ok, now I have found a solution that seems to work on both my local desktop version and online.

I created the following DAX based on the UTC time that I have from my SQL server (CreatedUTC).

= DateTimeZone.SwitchZone([CreatedUTC],DateTimeZone.ZoneHours([CreatedUTC])+2,0)

Thanks for the help, it got me on the right way of thinking 🙂