Forum Discussion
Beginner Needs Help with DST
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:
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!
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
MaggieCommunity 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.
3 Replies
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
MaggieCommunity 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.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!
My fellow intern figured it out, nevermind, thanks a bunch!
