Forum Discussion

Neala's avatar
Neala
Regular Visitor
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

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

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

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