Forum Discussion
DateTimeZone not recognized when using DateTimeZone.SwitchZone(DateTime.LocalNow(),3,0)
Hello,
I want to add to my reports the date and time when report was refreshed.
I have found the approach, but the problem is that in Power BI Service time zone is different, so I need to adjust it.
I have tried this:
let
Source = #table(type table[Date Last Refreshed=datetimezone], {{DateTimeZone.SwitchZone(DateTime.LocalNow(),3,0)}})
in
Source
Get error:
Source = #table(type table[Date Last Refreshed=datetimezone], {{DateTime.LocalNow()}}),
#"Added Custom" = Table.AddColumn(Source, "My time", each DateTimeZone.SwitchZone([Date Last Refreshed],3,0))
in
#"Added Custom"
Hi sandra_p ,
You may select on the new created datetime column, go to Transform tab, choose Data Type and select Date/Time/TimeZone instead of Date/Time, don't forget to click button "Close & Apply".
For reference:
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328
https://radacad.com/solving-dax-time-zone-issue-in-power-bi
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
sandra_p , can share the sample data . If can you are UK data format, make sure setting is correct
Hello,
Thank you for answer.
Everything is ok with format. I need to see the time 3 hours ahead in Power BI Service.
On my computer time is correct, because I have my local time zone on it.
Once report is published - time is 3 hours back once report is refreshed.
This function https://docs.microsoft.com/en-us/powerquery-m/datetimezone-switchzone - is supposed to switch timezone - that is what I need. But once I use it - it throws error (mentioned in my post) even though - the field I pass in it (see my code) is DateTimeZone type.
Hi sandra_p ,
You may select on the new created datetime column, go to Transform tab, choose Data Type and select Date/Time/TimeZone instead of Date/Time, don't forget to click button "Close & Apply".
For reference:
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328
https://radacad.com/solving-dax-time-zone-issue-in-power-bi
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Sandra,
Your best shot is to take the UTC time, and convert it to your desired daylight savings time as described here: https://gorilla.bi/power-query/last-refresh-datetime/
That works with this logic:
let UTC_DateTimeZone = DateTimeZone.UtcNow(), UTC_Date = Date.From(UTC_DateTimeZone), StartSummerTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday), StartWinterTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday), UTC_Offset = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1, CET_Timezone = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset) in CET_TimezoneThe important aspects here are:
Returning a fixed UTC datetimezone value, regardless of the refresh location of the server. Details here:
https://powerquery.how/datetimezone-utcnow/
And then switching the datetimezone value to the right zone as described here:
https://powerquery.how/datetimezone-switchzone/
Hope that helps - Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
