Forum Discussion

debarbanson's avatar
debarbanson
Regular Visitor
4 years ago

Fix UTC issue with date field

Dear all,

 

I am struggling quite a bit with the following. Have googled a lot, read many potential solutions but fail to get it to work.

Scenario:

We are running a Power App where the date/time of a voyage of a vessel is registered.

This date/time is send upon save to a Sharepoint Online list. One record is saved with a date/time 1-2-2022 00:00 in the app and also shown like that in the Sharepoint list.

Once I load this record into Power BI, it shows 31-1-2022 23:00 for that specific record, so it adjusts for UTC.

 

Problem is that based on this date/timestamp, the revenues of this voyages are shown in the month January upon filtering, where it should end up in February.

 

I want to add a custom column in Power Query which takes just the date from the date/time field, but I need it to be 1-2-2022 instead of 31-1-2022. So I'm not interested in the specific time, I just need the date but in my local timezone (the Netherlands).

This should not only work in the desktop version but more importantly also in the Power BI Service since the report is published.

 

What is the way to deal with this? have read stuff about duration and so forth but I need to take into account summer/winter time difference as well.

Any help is much appreciated since I've been struggling for many hours/days now and once I publish it keeps on showing the sale in the month January.

 

Many thanks in advance!

Need Help
Tips and Tricks

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi debarbanson 

     

    Try to use DateTimeZone functions in your custom column

     

     

    DateTimeZone.SwitchZone(DateTimeZone.From([Date]),1)

     

    • debarbanson's avatar
      debarbanson
      Regular Visitor
      4 years ago

      Hi Vera,

      Many thanks for this one. It converts the date correctly to 1-2-2022 00:00:00 +01.00

      DataType is ACB123. Once I convert to Date it shows 1-2-20222. This is also the field I use to link to the Calendar.
      In the desktop environment the income related to this voyage properly shows up in February but the moment I publish it once again ends up in January. Any thoughts?

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    When you first bring in your data, convert that column to datetimezone (not datetime), and then you can use the expression below to remove the zone component prior to extracting the date.

     

    Date.From(DateTimeZone.RemoveZone([InputDT]))

     

     

    Pat

    • debarbanson's avatar
      debarbanson
      Regular Visitor
      4 years ago

      Hi Pat, many thanks for your reply.
      Followed your steps, however when I change the field into Date/Time/Timezone it still shows 31-1-2022:

      and then after your code:

      Beginning to think there might be something wrong with my timezone? Would have expected that after the change to date/time/timezone it would have shown 1-2-2022 00:00:00 +01.00. Any suggestions?