display date/times in local timezone rather than UTC

[po]

Hi,

From my understanding service display s dates/times in UTC format.

If in U.K. what is the best way to ensure dates/times always displayed in local time zone (i.e. as clocks move forward 1 hour in March) uses british summer time automtically and then switch back in October when clocks go back an hour.

Is there a setting at tenant level whereby could say all dates/times to be displayed in local time zone?

Thanks

[Anonymous · Accepted solution]

Hi @po ,

Powerbi Service now only supports utc time, if you want to display the local time, you can create a custom column in Query Editor use DateTime.LocalNow() and #duration() function.

https://docs.microsoft.com/en-us/powerquery-m/sharpduration 

For example:

DateTime.LocalNow()+#duration(0,2,0,0)

Best Regards,

Jay

[ripstaur]

This is unbelievable. How could it be so hard to display a date-time stamp in the local time zone? 

So I have a table that I created, using the LocalNow() function that I want to use in a card to show the last refresh time and date. It should be straightforward, but I apparently have to do something to convert that time to local time (PBI only uses UTC). I have been looking in the communities for the last two hours and have not found anything useful. 

Here is my current query:

let

Source = DateTIme.From(DateTIme.LocalNow())

#"Convert to table" = TableFromValue(Source)

in

#"Convert to table"

It yields a date and time, e.g., 6/26/24, 3:58:47 PM

That would be OK, but it's 11:58:47 AM where I live and work. I want THAT time to show up. What do I need to add, or how do I need to otherwise modfy my query to make that result happen?

[po]

Hi,

Thanks for confirming.

[aj1973]

Hi @po 

It depends on where do you want use and display the Time. You can use DAX or M code.

[po]

Hi,

Thanks for reply.

Do you have an example.

If have datetimes which stored in database as utc and want these to show in reports as the uk equivalents we could chnage these at semantic layer on database but if wanted to do this in power bi how is this best achieved?

Thans

[aj1973]

@po 

Use Power Query, click on the calendar/text and then Using Locale

[po]

Thanks for reply

[aj1973]

Welcome,

Did you do it? if so please mark this thread as accepted for the rest of the community

[po]

Hi,

Had thought was working but was just using the cached version in service of the desktop version.

After refresh in service not working showing utc.

o.k. in desktop but not in service.

if use now() when time in u.k. is 9.30 a.m. service shows as 8:30 a.m. i..e utc time.

How can we ensure dynamcially that now(*) and similar finctions will alwys evaluate tp correct uk time all days of the year dynamically?

Thanks 

[aj1973]

@po 

If it is working in your Desktop and not in the service then check the Date/Time of your browser.

[po]

Thanks for reply.

browser has english (united kingdom) and uk dates

Found this link below which looks like takes into account dst 

https://community.powerbi.com/t5/Desktop/How-to-Get-Last-Refreshed-Date-in-local-DST-time-in-5-click...

[aj1973]

@po 

Did you use Power Query (like the screen shot I showed you previousely) or M code to change the Date column to Locale??

[po]

I used locale as pre your screenshot

[aj1973]

Great,

I would be nice to mention that reply (screen shot) as accepted solution for the rest of the community to know how to solve it.

Thanks

[po]

Hi,

Tried now() function by iteslf and shos as uk time correctly in both desktop and service.

Notice is a seeting at file level which already set to uk as per below.

Is it sufficient to ensure this is set as expected without having to go into individual datetime columns and set locale there?

Thanks