Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Convert Date/Time in UTC to Local Time with Daylight savings

Hello,   I'm not sure where is the better place to do this manipulation, but I have a column with the date/time as following: The time is in UTC and I want to convert this time to Mountain Day...
Need Help
  • gpoggi's avatar
    gpoggi
    7 years ago

    Hi Anonymous ,

     

    I think there are many ways, for example I tried to find a pattern in order to catch the November first Sunday or March second Sunday, and for your specific needs, maybe this custom function could work:

     

    (datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),

isSummerTime =	(date = SecondSundayOfMarch and time >= #time(1,0,0))
	        or
		(date > SecondSundayOfMarch and date < firstSundayOfNovember) 
		or 
		(date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (7 - Number.From(isSummerTime))*-1,

MDT = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0)

in
    MDT


    So for dates from March Second Sunday at 1:00am until November First Sunday at 12:59:59am you will get your datetime - 6 hours and for dates from November First Sunday 1:00am until March Second Sunday at 12:59:59am you will get your datetime - 7 hours

    According to Saint Google, the time is changed after 1:00am if you need it to be changed after 12:00am instead just remove first and last condition from isSummerTime

    If you have any question or if you find any error on the code, just let me know.

     

     

    Regards,

     

    Gian Carlo Poggi

     

     

  • gpoggi's avatar
    gpoggi
    7 years ago

    Sure Anonymous ,

     

    Right click on Queries pane and add a new Blank Query:

     

     

    Then right click on this new query and select Advanced Editor:

     

     

     

    In this new window erase all, paste the my code and click DONE:

     

     

     

    Now that query was converted into a function, you can rename it if you like, for example to "UTC_to_MDT":

     

     

     

    Then in order to use this function in your table you have different options, one option is going to your query or table, then click on Add Column / Invoke Custom Function, then put a name to this new column, select your function (in my case UTC_to_MDT) and select the column from your table you need to apply this function to (in my case "Date"):

     

     

     

    And then you will see the new date added :

     

     

     

    Hope this helps.

     

    Regards,

     

    Gian Carlo Poggi

andresballen's avatar
andresballen
New Member
4 years ago

Hey! I created this DAX formula to convert from UTC to PDT. Considering the following rules:

 

Daylight Saving: Pacific Daylight Time (PDT) is a daylight saving/summer timezone, however during winter some places switch clocks for one hour back and observe Pacific Standard Time (PST).

 

 

Start: Pacific Daylight Time (PDT) started on Sunday, March 14, 2021 at 2:00 am local time and clocks were set one hour forward to Sunday, March 14, 2021, 3:00 am. Daylight saving starts annually the on second Sunday of March

 

 

End: Pacific Daylight Time (PDT) ends on Sunday, November 7, 2021 at 2:00 am local time and clocks are set one hour back to Sunday, November 7, 2021, 1:00 am local standard time instead. Daylight saving ends annually the on first Sunday of November

 

UTC to PDT = 
VAR CurrentDate = DATE(YEAR('Date'[Date]),MONTH('Date'[Date]),DAY('Date'[Date]))
var CurrentTime = TIME(HOUR('Date'[Date]),MINUTE('Date'[Date]),SECOND('Date'[Date]))
var March = DATE(2022,3,1)
var November = DATE(2022,11,1)
VAR SecondSundayMarch = FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(CurrentDate) && 
        MONTH('Date'[Date]) = MONTH(March) && 
        DAY([Date]) > 7 && 
        DAY([Date]) < 15 && 
        WEEKDAY([Date],1) = 1)
VAR FirstSundayNov = FILTER(
        ALL('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(CurrentDate) && 
        MONTH('Date'[Date]) = MONTH(November) && 
        DAY([Date]) >= 1 && 
        DAY([Date]) < 8 && 
        WEEKDAY([Date],1) = 1)
VAR IsSummerTime = OR(AND(CurrentDate = SecondSundayMarch, CurrentTime >= time(9,0,0)),  OR(AND(CurrentDate > SecondSundayMarch, CurrentDate < FirstSundayNov), AND(CurrentDate = FirstSundayNov, CurrentTime <= time(8,59,0))))
VAR TimeDiff = TIME(8-IsSummerTime,0,0) // If is summer time diff = 7 else time diff = 8
RETURN CurrentDate-TimeDiff

 

  • nehajadhav166's avatar
    nehajadhav166
    Icon for Resolver I rankResolver I
    3 years ago

    Anonymous Can you please explain in steps more how do you used this formula?

    I have table with UTC timestamp. Is this formula needs to be created as measure in same table?

     

    Thanks,

    Neha

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi nehajadhav166 

      I've only used this in M in Power BI Services. 

      1. Create a custom function

      (datetimecolumn as datetime) =>
    let
    date = DateTime.Date(datetimecolumn),
    time = DateTime.Time(datetimecolumn),
    lastSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),
    lastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
    isSummerTime =	(date = lastSundayOfMarch and time >= #time(2,0,0))
    or
    (date > lastSundayOfMarch and date < lastSundayOfOctober)
    or
    (date = lastSundayOfOctober and time <= #time(2,0,0)),
    timeZone = 1 + Number.From(isSummerTime),
    CET =
    DateTime.From(date)
    + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
    + #duration(0, timeZone, 0, 0)
in
    CET

      2. Invoke custom function on the date column of your table to create a new column with the timestamp convertion

       

      I'm not able to provide you with a more detailed description at this point, and my screenshots are in Norwegian, but I hope this at least will help you a bit along the way.