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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
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!