cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
po
Post Prodigy
Post Prodigy

display date/times in local timezone rather than UTC

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

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

14 REPLIES 14
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

Hi,

 

Thanks for confirming.

aj1973
Community Champion
Community Champion

Hi @po 

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

po
Post Prodigy
Post Prodigy

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
Community Champion
Community Champion

@po 

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

aj1973_0-1618320130524.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

po
Post Prodigy
Post Prodigy

Thanks for reply

aj1973
Community Champion
Community Champion

Welcome,

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

po
Post Prodigy
Post Prodigy

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
Community Champion
Community Champion

@po 

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

po
Post Prodigy
Post Prodigy

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
Community Champion
Community Champion

@po 

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

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

po
Post Prodigy
Post Prodigy

I used locale as pre your screenshot

aj1973
Community Champion
Community Champion

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

po
Post Prodigy
Post Prodigy

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

 

 

regsettings.JPG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors