Forum Discussion
DateTime does not consider DLS
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.
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.
2 Replies
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 SourceHope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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.
