Forum Discussion

Avits's avatar
Avits
Regular Visitor
2 years ago
Solved

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 t...
Need Help
  • Avits's avatar
    Avits
    2 years ago

    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.

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Avits
Regular Visitor
2 years ago

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.