Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Timezone reverts back to UTC when published?

Hi all,

This is what data looks like in PBI desktop:

personalwafer_0-1660754893639.png

 

This is what it looks like after it is published:

personalwafer_1-1660754954788.png

 

The Technician should have $720 charges every day starting May 23 but, after it is published, the charges moved 1 day and start on May 24 instead. 

 

Is this the issue with timezone reverts back to UTC when published? We assume this is the issue and trying the following with no success:

 

- DateTime.AddZone

- DateTimeZone.SwitchZone

- DateTimeZone.ToLocal

- We got in touch with MS Support Engineer who told us to follow what's on this video: https://youtu.be/2kmFfbOeFJg

 

We always get the same problem with these 4, but then we tried the following:

 

- Using the following codes:

let localdate = DateTimeZone.RemoveZone(DateTimeZone.SwitchZone([UTCDate],-6)),

year = Date.Year(localdate),

DSTEnd = Date.StartOfWeek( #datetime(year,3,7,2,0,0),Day.Sunday),

DSTStart = Date.StartOfWeek( #datetime(year,11,7,2,0,0),Day.Sunday),

EndTC = Number.From( Text.From(year) & Text.PadStart( Text.From( Date.Month(DSTEnd)),2,"0") & Text.PadStart( Text.From( Date.Day(DSTEnd)),2,"0") & "02"),

StartTC = Number.From( Text.From(year) & Text.PadStart( Text.From( Date.Month(DSTStart)),2,"0") & Text.PadStart( Text.From( Date.Day(DSTStart)),2,"0") & "02"),

localTC = Number.From( Text.From(year) & Text.PadStart( Text.From( Date.Month(localdate)),2,"0") & Text.PadStart( Text.From( Date.Day(localdate)),2,"0") & Text.PadStart( Text.From( Time.Hour(localdate)),2,"0")),

tzshift = if localTC < StartTC and localTC >= EndTC then -7 else -6, localdatetime = DateTimeZone.SwitchZone([UTCDate],tzshift)

in localdatetime

 

Using the above codes, we got the following result:

personalwafer_2-1660755342647.png

 

The codes fix the $720 charges, but the $1440 charges is now incorrect. Also, we got this mix result only if we set the date as Date/Time/Timezone, if we set it as Date or Date/Time, we would get the same previous result as shown on the second secreen shot above. 

 

MS Dynamics feeds live data onto Power BI and date is in UTC and displayed in the user time zone.

 

Much appreciated for any help!

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Don't fight the UTC.  Convert your local timestamps to UTC before you load the data. Then either let the browser do its thing, or implement text based measures to convert back for display.

 

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Don't fight the UTC.  Convert your local timestamps to UTC before you load the data. Then either let the browser do its thing, or implement text based measures to convert back for display.

 

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors