DateTime does not consider DLS

[Avits]

let

    Source = #table(type table[Time Last Refreshed=datetime], {{DateTime.LocalNow()}})

in

    Source

Gives me the dateTime and is correct on the desktop, but in the browser it is missing the DLS (UK = UTC + 1 currently). So the time is 1 hour out in the browser.

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 1 else 0,

  Source           = #table(type table[Time Last Refreshed=datetime], {{DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)}})

in

  Source

Gives me a result I can't use (It quite rightly is in UTC format and has the 1 offset at the end reflecting the summer time here in the UK).

I'm new to the scripting and can't figure out how to get the current date & time with DLS in a simple DateTime format!

Any help would be appreciated.

[Anonymous]

Hi @Avits ,

You can try using the duration #function:

#duration - PowerQuery M | Microsoft Learn

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 1 else 0,
 
  Source           = #table(type table[Time Last Refreshed=datetime], {{ UTC_DateTimeZone + #duration(0,0,UTC_Offset,0) }})
 
in
 
  Source

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Avits · Accepted solution]

That would probably work.

What I ended up doing was using another approach refrenceed elsewhere on this forum:

(datetimecolumn as datetime) =>

let

date = DateTime.Date(datetimecolumn),

time = DateTime.Time(datetimecolumn),

// From https://www.gov.uk/when-do-the-clocks-change

// In the UK the clocks go forward 1 hour at 1am on the last Sunday in March,

// and back 1 hour at 2am on the last Sunday in October.

// Last Sunday in March

ForwardDate = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),

// Last Sunday in October

BackDate = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday),

isSummerTime =

               (date = ForwardDate and time >= #time(1,0,0))

            or

               (date > ForwardDate and date < BackDate)

               or

               (date = BackDate and time < #time(1,0,0)),

#

timeZone = Number.From(isSummerTime),

Europe_London = datetimecolumn + #duration(0, timeZone, 0, 0)

in

    Europe_London

That did the trick for me.

Of course, it is slightly odd that PBI Desktop gets it right, so the times you see on the desktop and the web are different by an hour for use - whatever we do!

Not a major issue, because the users are on the web.

Thanks for your response.