Local Date not UTC in PBI Service

Power BI Online Service
1

DateTimeZone, One would think that Microsoft would make this easier to manage in the PBI Service. I’m in Australia (UTC +10) Here’s my issue, no matter what I try in Power Query, I cannot get the dates to display correctly for the current day in Australia. I’m using a few visuals to display the sales for the current date and the day before but they do not update to the correct date in AU until 10:00 local time (00:00 UTC). I’ve tried everything I’ve used in other models - no joy. There are numerous posts and videos out there about this but nothing to directly address the time zone problems globally in the PBI service. Yes, I’ve set the locale in both PBI Service and the PBI Desktop. I am using @Melissa dates table from a post here in the forum.

Appreciations in advance!

I have a dates table that contains a standard ‘Date’ column and ‘Current Date’, ‘Yesterday’, DayOffset.

#“Added Current Date” = Table.AddColumn(#“Renamed Columns”, “Current Date”, each Date.From(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),10,0))),

#“Added IsCurrentDay” = Table.AddColumn(#“Added Current Date”, “IsCurrentDay”, each if [Date] = [Current Date] then “Current Date” else “Other Date”),

#“Added Yesterday” = Table.AddColumn(#“Added IsCurrentDay”, “Yesterday”, each Date.AddDays(Date.From(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),10,0)),-1)),

#“Added Today-Yesterday-Other” = Table.AddColumn(#“Added Yesterday”, “Today-Yesterday-Other”, each if [Date] = [Yesterday] then “Yesterday” else if [Date] = [Current Date] then “Current Day” else “Other Day”)

2

Hi @mkaess,

i’m sure you checked this, did you check your own computer system?

Its just a thought.

thanks
Keith

3

Like me, I go back to the basics too .:slight_smile: Yes, I have. The PBI service regional setting is ‘Use browser settings’. Mine is set to English Australia as are the language settings in Windows. The problem is the ‘Yesterday-Today’ slicer which is a column in the date table. In Power Query, it compares the current date value to the date column.

One thing I did not mention is that the report itself is a live query to a shared PBI dataset.

4

Hi @mkaess

Here is great explanation from Ruth to convert UTC to local timezones.

Hope this helps

5

Hi @mkaess,

For me a set up like this returns local date time in both Power BI Desktop and the Power BI Service.
This is what I call a Last Refresh table and I’ve set the UTC to Local offset hours to +10.

let
    Locale = DateTime.AddZone( DateTime.From( DateTimeZone.FixedUtcNow()), 10),
    ConvertToTable = #table(1, {{Locale}}),
    RenameColumn = Table.RenameColumns(ConvertToTable,{{"Column1", "Last Refresh"}}),
    ChangeType = Table.TransformColumnTypes(RenameColumn,{{"Last Refresh", type datetimezone}}),
    LocalDate = Table.AddColumn(ChangeType, "Date", each DateTime.Date([Last Refresh]), type date),
    LocalTime = Table.AddColumn(LocalDate, "Time", each DateTime.Time([Last Refresh]), type time)
in
    LocalTime

.
I hope this is helpful.

6

Hi @mkaess did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

7

@Melissa that may be where I got this script. I use it to show the date/time the report was last refreshed. I’m just not sure how I can use this in a slicer to show a card that displays today’s sales and one that shows yesterday. My DayOffset value doesn’t work in the PBI Service.

8

I bring this Last Refresh table in as a supporting table with no relationship to the Date table. Create a simple measure with MAX (or whatever aggregator you prefer) to extract the value, then use that to filter Dates or calculate yesterday’s date which is just the current value -1 and so on.

I hope this is helpful.

9

Nothing I’ve tried in power query works. When I upload a new version of the pbix file, it’s correct but once the time changes again, it reverts back to UTC. I’ve added the calendar table that I’m using and if anyone has any ideas I would be forever grateful. RADACAD Date Dimension.pbix (242.0 KB)

10

@mkaess,

Because of local Dutch time I use a timefix in DAX with a measure like:

TimeOffset = 2/24

I have made new date columns like:

[your datecolumn] + TimeOffset

There are downsides of creating these new calculated columns because of the memory it will occupy, but it does the job for me.

Hope it helps.

Daniel

11

Hi @mkaess,

I’ve replaced all DateTimeZone.LocalNow() into DateTimeZone.FixedUtcNow()
This way the base DateTime will be the same in both Power BI Desktop as in the Service

image
image783×481 5.31 KB

Here’s your updated file. eDNA - Date Dimension with TimeZone.pbix (237.0 KB)
I hope this is helpful

2 Likes
12

Thanks @Melissa - I’m not having any success at all with this. When I upload the eDNA dates table to our PBI instance, the results are not correct. They are still UTC and not corrected for local date/time until 00:00 UTC. I have checked the PBI Service settings and it all seems correct. Maybe I’m missing something here. What’s frustrating is that I have done this for another client and it worked fine.

Annotation 2020-09-08 081958
Annotation 2020-09-08 0819581450×714 15.4 KB

13

@mkaess this is odd - because they should be closer to each other. Both have UTC time at an offset of 10 hours - the difference should be explained by dataset refresh time at most - when you are monitoring results in the Service.

Keep in mind that the ones at the bottom are DAX functions and should be considered volatile, I only included them as a reference value…

image
image914×523 16.8 KB

14

After all of that, I ended up taking some of what @Melissa suggested and modified it into a calculated column ‘Yesterday-Today-Other’ in my dates table and used this as the filter in the visual. I try to avoid calculated columns as much as possible for the reasons mentioned in an earlier reply but this got me the results I needed. THANKS all for the help. It was a community effort for sure!

Yesterday-Today-Other =
VAR CurrDate =
DATEVALUE ( FORMAT ( UTCNOW () + DIVIDE ( 10, 24 ), “General Date” ) )
RETURN
SWITCH (
TRUE (),
Dates[Date] = CurrDate, “Today”,
Dates[Date] = CurrDate - 1, “Yesterday”,
“Other”
)