Converting a date fact from UTC to local time

Start off with an Aggregation which shows time in UTC, like the example below (note this fact is available as of 16 Build 39)

Next you need to add a scalar valued function to your database, which must be SQL Server 2016 or above:

CREATE FUNCTION [dbo].UTCDateToLocal
(
 @d datetime,
 @tz nvarchar(1024)
)
RETURNS datetime
AS
BEGIN
 declare @d2 datetimeoffset;
 set @d2 = (select @d AT TIME ZONE @tz);
 return dateadd(mi,datepart(tz,@d2),@d);
END

Now add a custom function fact like this:

Now you can set the parameter of the custom function fact to Create Date.

The list of available time zones can be found here:

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

Note that you can’t just put the scalar valued function SQL into the custom function fact because (at the time of writing) SQL Server has a bug compiling complex ‘AT TIME ZONE’ statements.