Time different in PBI Desktop and PBI Service

Power BI
1

I have an odd problem regarding the time of transactions for the same model in PBI Desktop and the PBI Service.

In PBI Desktop the last transaction time in a day shows as 16:15:47 which is correct here in the UK as our time is UTC + 1 (British Summer Time).

In the PBI Service the exact same transaction is displaying as 15:15:47, which is UTC.

This is misleading for users of course.

Any thoughts welcome.

Paul

2

Hi @PaulBoyes,

You can handle timezones in power query, see this post for example

I hope this is helpful

2 Likes
3

Hi Melissa

Thank you for your reply.

I would prefer not to use a calculated column as this applies to a transaction file that could grow to millions of records which would make the refresh onerous.

Ideally I would like to use a measure as the time is simply a display issue. But, I would like the time to display for the user wherever they are in the world.

Having said that, this is only an irritant when we are on daylight saving. For a user on a different continent it would be obvious that the time is UTC.

Best regards

Paul

4

In the Format box of a date / time if you see an * (asterisk) that format will reflect the date / time settings of your operating system.
image
This is what I change when in the service dates are shown in US format instead of UK format.
Maybe this will help?..

5

Hi
Thank you for your contribution, much appreciated. I have that setting already.

I see you are in health care and there is an initiative that we are involved in that is specifically in that sector that may be of interest. Please feel free to get in touch at any time.

Best regards

Paul

6

Interesting @PaulBoyes , will do…

7

AFAIK there is no way to ‘detect’ a users region in the PBI Service at this time. However you could add a static table with: user principal name, default region or timezone and if daylight saving is applicable, with these variables you should be able to calculate the correct display time.

Try something like this, amend to your needs.

BST correction = 
VAR _Today = TODAY()
VAR _Year = YEAR(_Today)
VAR BST_Start = DATE( _Year, 3+1, 1) - WEEKDAY(DATE( _Year, 3+1, 1),1)+1 //last Sunday of March
VAR BST_End = DATE( _Year, 10+1, 1) - WEEKDAY( DATE( _Year, 10+1, 1),1)+1  // last Sunday of October
VAR _OffsetHrs = 1
RETURN

IF( _Today > BST_Start && _Today < BST_End,
    SELECTEDVALUE( DateTime[UTC] ) + (_OffsetHrs/24),
    SELECTEDVALUE( DateTime[UTC] )
)

I hope this is helpful

8

Thank you Melissa, much appreciated.

Paul

9

If you’re lucky enough to have either Power BI Premium per capacity or Power BI Premium per user, you could use the DAX function USERCULTURE().

Returns the locale (language code-country code) for the current user, determined by the operating system, browser settings, or Power BI service.

1 Like