How to change time on Column according to daylight savings

[MarcioL]

Hi All!

I have some data coming out from a server in a different state so to analyse the data I need to adjust the time to our Timezone and daylight savings.

How can I add a new column next to "Due_Date" that returns :

-> plus 2 hours if the date is from First Sunday of April

-> plus 3 hours if the date is from First Sunday of October 

I'm relatively new to Power BI so would appreciate any help.

Thank you!

[edhans]

There are probably an number of different approaches to this. I created a Daylight Savings calendar table since you cannot rely on DST being the same time as laws change. Below is my table. The start and end date of the DST period each year.

I then created a query that pulled that. My SOURCE row is just a link to the SQL Server data tables. Could be a spreadsheet, sharepoint list, whatever. You would need to alter or remove the 2nd step depending on your source.

let
    Source = SQL_DATA,
    dbo_tblDaylightSavings = Source{[Schema="dbo",Item="tblDaylightSavings"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_tblDaylightSavings, each [dtDSTStart] <= DateTime.Date(DateTime.LocalNow()) and [dtDSTEnd] > DateTime.Date(DateTime.LocalNow())),
    #"Counted Rows" = Table.RowCount(#"Filtered Rows")
in
    #"Counted Rows"

That will return a 1 if we are in a DST period, or 0 if not.

Then I use pmDST within this query to add 0 or 1 hours to my default timezone, which for me is -8.

let
  Source = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-8 + pmDST,0),
  #"Converted to Table" = #table(1, {{Source}}),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "RefreshDate"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"RefreshDate", type datetimezone}})
in
  #"Changed Type"

You would need to alter the -8 to be where you are located.

[MarcioL]

Thank you for your reply edhans.

For what I understand from your codes that will return the last refreshed date to my Timezone having in considerations the daylight savings, correct?

What I need is to have each row from the column "Due_Date" correcetd to my timezone and daylight savings. Not sure if this is possible.

[edhans]

Yes. My first query will return a 0 or 1 if it is not or is during DST. You could just add an hour  or not to your default times.  Or if you needed to add 2, then add 2 plus my pmDST, so it would add 2 or 3.

[MarcioL]

thanks edhans but that is not what I am looking for.

Maybe I didnt explain properly.

In my case I extract a table from a database with all the last 30 days (date and time) of the latest time a truck left our facilities. That database is in another state so daylight saving hours are different from were our facilities are located. For this reason I need to convert each single day from that table into my timezone and daylight savings.

Here is +3 hours between first sunday of october to first sunday of april. +2 during the rest of the year.

[v-jiascu-msft · Accepted solution]

Hi @MarcioL,

Try this formula, please.

Column =
VAR currentLineYear =
    YEAR ( [Due_Date] )
VAR firstSunOct =
    MINX (
        FILTER (
            CALENDARAUTO (),
            YEAR ( [Date] ) = currentLineYear
                && MONTH ( [Date] ) = 10
                && WEEKDAY ( [Date], 2 ) = 7
        ),
        [Date]
    )
VAR firstFunApr =
    MINX (
        FILTER (
            CALENDARAUTO (),
            YEAR ( [date] ) = currentLineYear
                && MONTH ( [Date] ) = 4
                && WEEKDAY ( [Date], 2 ) = 7
        ),
        [Date]
    )
RETURN
    IF (
        [Due_Date] >= firstSunOct
            || [Due_Date] <= firstFunApr,
        [Due_Date] + TIME ( 3, 0, 0 ),
        [Due_Date] + TIME ( 2, 0, 0 )
    )

Best Regards,

Dale

[snowrider1799]

Hi @v-jiascu-msft ,

Do you have this logic in Power Query / M language? I need to show in the table itself instead of DAX.

Also, not sure if you have any solution, this is my scenario:

1. I am using PBI Dataflows, I am located in Malaysia.

2. The PBI Data Gateway is at US. 

3. We need to create a "Last Refresh date" in our dataflows table, as a single column, to allow user check if the dataset is latest. 

4. But the date is reflecting according to US timezone, instead of Asia's. 

Do you have any idea how to achieve this in the settings? If not, I would appreciate if you could provide the solution in M language. 

Thank you!