Beginner Needs Help with DST

[Neala]

Hi! I'm pretty much brand new to Power BI and Power Query. I am a college student working an internship where I was asked to self-teach myself Power BI. The project I am currently working on needs to have several dates changed to Eastern Standard time, which would also include Daylight Savings Time when applicable. I found this tutorial:

https://selfservedbi.com/2018/12/15/converting-from-utc-to-local-timezone-in-power-bi-and-azure-anal...

Which is great! But I'm not sure where to put that code? Does it go into its own column or into advanced editor? I need to use the code multiple times (I think) as I need to change multiple dates, such as order created date, order processed date, and order fulfilled date. Can anyone help me out? I would really appreciate it, thanks so much!

[v-juanli-msft · Accepted solution]

Hi @Neala 

For these code

CurrentYear = Date.Year(DateTime.LocalNow()),
 
FirstSundayInApril = if Date.Month(Date.StartOfWeek(#date(CurrentYear,4,1),Day.Sunday)) = 3 then Date.AddDays(Date.StartOfWeek(#date(CurrentYear,4,1),Day.Sunday),7) else Date.StartOfWeek(#date(CurrentYear,4,1),Day.Sunday),
 
FirstSundayInOctober = if Date.Month(Date.StartOfWeek(#date(CurrentYear,10,1),Day.Sunday)) = 9 then Date.AddDays(Date.StartOfWeek(#date(CurrentYear,10,1),Day.Sunday),7) else Date.StartOfWeek(#date(CurrentYear,10,1),Day.Sunday),
 
CurrentDate = Date.From(DateTime.LocalNow()),
 
AustralianTime = if CurrentDate >= FirstSundayInApril and CurrentDate < FirstSundayInOctober then DateTime.LocalNow() + #duration(0,10,0,0) else DateTime.LocalNow() + #duration(0,11,0,0)

Home->Edit queries->Add column->Add custom column,

Write these code in each custom column,

there are five custom column as above.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Neala]

Thank you so much!

So, I'm trying to do this for UTC time changed to Eastern Standard /Eastern Daylight Time. I feel like I changed this correctly, except that the date should give March 10th and it is giving March 3rd. Any ideas? @v-juanli-msft 

Here is my edited code: 

if Date.Month(Date.StartOfWeek(#date(CurrentYear,3,2),Day.Sunday)) = 2 then Date.AddDays(Date.StartOfWeek(#date(CurrentYear,3,2),Day.Sunday),7) else Date.StartOfWeek(#date(CurrentYear,3,2),Day.Sunday)

Also, will this work for all years or only this year? I want this to work for data going back to 2016.

Thank you so much!

[Neala]

My fellow intern figured it out, nevermind, thanks a bunch!