Forum Discussion
Community ChampionDMV MDSCHEMA_CUBES
There is a way to convert the utc to client locale timestamp.
$SYSTEM.MDSCHEMA_CUBES returns timestamp in utc as the pbi server is in utc. The Analysis server does not have any built-in server offset function either in DAX/MDX/DMV ( I would be very happy to be proved wrong). So therfore, there is no way to utilize any SSAS function to convert server timezone to a prefereed timezone LIKE MIGHTY SQL.
The first step is to create a datamart, even if there is no data ingested in it. The purpose of this datamart is to be able to take advantage of the SQL server that comes with it as SQL server has built-in TIMEZONE functions. So in my case I would do it like this
let col = [dateTime] yr = Text.From(Date.Year(col)), month = Text.End("0"&Text.From(Date.Month(col)),2), day = Text.End("0"&Text.From(Date.Day(col)),2), hour = Text.End("0"&Text.From(Time.Hour(col)),2), minute = Text.End("0"&Text.From(Time.Minute(col)),2), second = Text.From(Time.Second(col)), val = yr&"-"&month&"-"&day&" "&hour&":"&minute&":"&second // 2024-05-31 17:07:41.373 // could I have avoided generating val in PQ? yes, if DMV had support for CAST/CONVERT /* BUT The query engine for DMVs is the Data Mining parser. The DMV query syntax is based on the SELECT (DMX) statement. Although DMV query syntax is based on a SQL SELECT statement, it does not support the full syntax of a SELECT statement. Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.*/ /* hence, PQ is required to generate val */ in Sql.Database("server.datamart.fabric.microsoft.com", "db", [Query="select CONVERT(DATETIME,convert(datetime,'"&Text.From(val)&"') AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') as t"])[t]{0}and this checks out on the server too
Super UserThat will not help as "Local" for the Power BI service means "UTC". You need to supply the shift parameter.
hackcrr 's approach is better but it will only work half of the year.
Community ChampionThis works as long as it is used in semantic model and not in dataflow. This correctly intetprets client locale, but fails in dataflow. Power bi should have provided a method without needing me to explicitly provide the 3rd parameter for DateTimeZone.SwitchZone
- lbendlin
there is no "client locale" in a dataflow, nor in a Power BI service semantic model. It is only applicable in a local PBIX or Excel file.
- smpa01
There is a way to convert the utc to client locale timestamp.
$SYSTEM.MDSCHEMA_CUBES returns timestamp in utc as the pbi server is in utc. The Analysis server does not have any built-in server offset function either in DAX/MDX/DMV ( I would be very happy to be proved wrong). So therfore, there is no way to utilize any SSAS function to convert server timezone to a prefereed timezone LIKE MIGHTY SQL.
The first step is to create a datamart, even if there is no data ingested in it. The purpose of this datamart is to be able to take advantage of the SQL server that comes with it as SQL server has built-in TIMEZONE functions. So in my case I would do it like this
let col = [dateTime] yr = Text.From(Date.Year(col)), month = Text.End("0"&Text.From(Date.Month(col)),2), day = Text.End("0"&Text.From(Date.Day(col)),2), hour = Text.End("0"&Text.From(Time.Hour(col)),2), minute = Text.End("0"&Text.From(Time.Minute(col)),2), second = Text.From(Time.Second(col)), val = yr&"-"&month&"-"&day&" "&hour&":"&minute&":"&second // 2024-05-31 17:07:41.373 // could I have avoided generating val in PQ? yes, if DMV had support for CAST/CONVERT /* BUT The query engine for DMVs is the Data Mining parser. The DMV query syntax is based on the SELECT (DMX) statement. Although DMV query syntax is based on a SQL SELECT statement, it does not support the full syntax of a SELECT statement. Notably, JOIN, GROUP BY, LIKE, CAST, and CONVERT are not supported.*/ /* hence, PQ is required to generate val */ in Sql.Database("server.datamart.fabric.microsoft.com", "db", [Query="select CONVERT(DATETIME,convert(datetime,'"&Text.From(val)&"') AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') as t"])[t]{0}and this checks out on the server too