Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
This is what data looks like in PBI desktop:
This is what it looks like after it is published:
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:
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!
Solved! Go to Solution.
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.
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.