DateTimeZone not recognized when using DateTimeZone.SwitchZone(DateTime.LocalNow(),3,0)

[sandra_p]

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:

Expression.Error: We cannot convert the value #datetime(2020, 6, 18, 11, 45, 34.8464175) to type DateTimeZone. Details: Value=18/06/2020 11:45:35 Type=[Type]

And I tried this:

let
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"

Same error happens.

Why "Date Last Refreshed" is not recognized as DateTimeZone even if it has this type?

[Rickmaurinus]

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_Timezone

The 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.

[amitchandak]

@sandra_p , can share the sample data . If can you are UK data format, make sure setting is correct

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

[sandra_p]

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.

[v-xicai · Accepted solution]

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.