DMV 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